使用sql插入语句增加非唯一字段
我有三个表 TelNo 、 RefNo 和 Ref_Check_No 。 RefNo 和 Ref_Check_No 分别包含 Telephone_Id 和 Entity_Id 列以及其他列,而 TelNo > 具有 Telephone_Id 和 Entity_Id。我想要做的是将 Ref_Check_No 中的所有记录插入 RefNo 表中。 TelNo 表包含每个Entity_Id 的Telephone_Id。所有提到的这些列都不是唯一的。
问题是我知道如何从 Ref_Check_No 表插入数据。但是,我似乎无法为每个 Entity_Id 插入 Telephone_Id 值。我想要做的是从 TelNo 表中检索每个 Entity_Id 的最大 Telephone_Id 号码,并为每个 将其增加 1使用插入语句在 RefNo 表中获取 Entity_Id。下面是我希望在 RefNo 表中实现的示例:
**TelNo Table** **RefNo Table**
Telephone_Id Entity_Id Telephone_Id Entity_Id
1 ABCD 4 ABCD
2 ABCD 5 ABCD
3 ABCD 6 ABCD
89 EFGH 7 ABCD
90 EFGH 96 EFGH
95 EFGH 97 EFGH
基本上,我想要做的是找到每个 Entity_IdTelephone_Id 最大数量强>(ABCD 和 EFGH)来自 TelNo 表,并为 RefNo 表中每个相应的 Entity_Id 将其加 1。
下面是我正在编写的代码,但我似乎无法正确执行。
INSERT INTO
RefNo
(
Telephone_Id,
Entity_ID,
ContactName
)
SELECT
(SELECT COUNT(*) FROM Ref_Check_No b WHERE(a.Entity_Id = b.Entity_Id)
ISNULL ((SELECT MAX(Telephone_Id) AS Telephone_Id FROM TelNo
WHERE (Entity_Id = a.Entity_Id)), 0) AS Telephone_Id,
Entity_ID,
ContactName
FROM
Ref_Check_No
有没有办法在 RefNo 中插入数据时使用相关子查询找到最大 Telephone_Id 号码,并为每个 Entity_Id 将其增加 1同时上桌?
I have three tables TelNo , RefNo and Ref_Check_No. RefNo and Ref_Check_No has columns Telephone_Id and Entity_Id respectively along with other columns, while TelNo has Telephone_Id and Entity_Id. What I would like to do is to insert all the records from Ref_Check_No into the RefNo table. The TelNo Table contains the Telephone_Id for each Entity_Id. All of these columns mentioned are not unique.
The thing is I know how to insert the data from the Ref_Check_No table. However, I can't seem to insert the Telephone_Id values for each Entity_Id. What I would like to do is to retrieve the MAX Telephone_Id number per Entity_Id from the TelNo table and increment it by 1 for each Entity_Id in the RefNo table using an insert statement. Below is an example of what I would like to achieve in the RefNo Table:
**TelNo Table** **RefNo Table**
Telephone_Id Entity_Id Telephone_Id Entity_Id
1 ABCD 4 ABCD
2 ABCD 5 ABCD
3 ABCD 6 ABCD
89 EFGH 7 ABCD
90 EFGH 96 EFGH
95 EFGH 97 EFGH
Basically what I want to do is to find the MAX Telephone_Id number per Entity_Id (ABCD and EFGH) from the
TelNo table and increment it by 1 for each corresponding Entity_Id in the RefNo table.
Below is the code that I was working on which I can't seem to get right.
INSERT INTO
RefNo
(
Telephone_Id,
Entity_ID,
ContactName
)
SELECT
(SELECT COUNT(*) FROM Ref_Check_No b WHERE(a.Entity_Id = b.Entity_Id)
ISNULL ((SELECT MAX(Telephone_Id) AS Telephone_Id FROM TelNo
WHERE (Entity_Id = a.Entity_Id)), 0) AS Telephone_Id,
Entity_ID,
ContactName
FROM
Ref_Check_No
Is there a way to find the MAX Telephone_Id number and increment it by 1 for each Entity_Id using a correlated sub query while inserting data in the RefNo table at the same time?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我很难理解你的例子。您想要在 RefNo 中为 Ref_Check_No 中的每条记录创建一行,并使用相同的 Entity_ID 和递增的 Telephone_ID 来标记每条记录?
如果是这种情况,请尝试以下操作(对于 MSSQL):
I am having a hard time understanding your example. You want to create one row in RefNo for each record in Ref_Check_No, labelling each with the same Entity_ID and an increasing Telephone_ID?
If that is the case, try this (for MSSQL):