使用sql server中另一个表的ID更新现有表的新外键列

fbcarpbf 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(337)

我有一个现有的表，我已经添加了一个新的列，它应该保存另一个（新）表中记录的id。
简化的结构是这样的：
客户表

[CustomerId] [GroupId] [LicenceId]  <-- new column

许可证表格<--新表格

[LicenceId] [GroupId]

许可证表中每个组有一定数量的许可证，不能分配给同一组中的客户。有多个组，每个组都有不同数量的客户和许可证。假设第一组有100个许可证，第一组有50个客户，每个客户都可以获得一个许可证。客户永远不会比许可证多。
样品

Customer
[CustomerId] [GroupId] [LicenceId]
 1            1         NULL
 2            1         NULL
 3            1         NULL
 4            1         NULL
 5            2         NULL
 6            2         NULL
 7            2         NULL
 8            3         NULL
 9            3         NULL

Licence
[LicenceId] [GroupId]
 1            1
 2            1
 3            1
 4            1
 5            1
 6            1
 7            2
 8            2
 9            2
 10           2
 11           2
 12           3
 13           3
 14           3
 15           3
 16           3
 17           3

Desired outcome

Customer
[CustomerId] [GroupId] [LicenceId]
 1            1         1
 2            1         2
 3            1         3
 4            1         4
 5            2         7
 6            2         8
 7            2         9
 8            3         12
 9            3         13

所以现在我必须做一次更新，给每个客户一个许可证，我不知道该怎么做。
我不能用光标。我似乎无法进行合并更新，因为按groupid将客户加入license表将导致多次命中。
如何在一个查询中为每个客户分配组中下一个可用的许可证ID？这有可能吗？

sql sql-server bulk

来源：https://stackoverflow.com/questions/62815264/update-new-foreign-key-column-of-existing-table-with-ids-from-another-table-in-s

2条答案

按热度按时间

wecizke31#

可以使用窗口函数：

with c as (
      select c.*, row_number() over (partition by groupid order by newid()) as seqnum
      from customers c
     ), 
     l as (
      select l.*, row_number() over (partition by groupid order by newid()) as seqnum
      from licenses c
     )
update c
    set c.licenceid = l.licenseid
    from c join
         l
         on c.seqnum = l.seqnum and c.groupid = l.groupid;

这将随机分配许可证。那真的只是为了好玩。最有效的方法是使用：

row_number() over (partition by groupid order by (select null)) as seqnum

在这种情况下，SQLServer通常避免额外的排序操作。
但是您可能希望通过其他方式对它们进行排序--例如，通过对客户ID的排序，或者通过某个日期列，或者其他方式。

赞(0）回复(0）举报 2021-07-24

iqxoj9l92#

戈登在回答中说得很好。让我把它分解成更简单的步骤。
第一步。使用row_number（）函数为客户分配seqnum。使用partitionbygroupid，使每个组中的数字从1开始。我会按客户ID订购
第二步。使用row_number（）函数为许可证分配seqnum。使用partitionbygroupid，使每个组中的数字从1开始。按许可证ID订购，因为您的任务是“为每个客户分配其组中的下一个可用许可证ID”。
现在使用这两个查询来更新customer表中的licenseid。

赞(0）回复(0）举报 2021-07-24

我来回答

使用sql server中另一个表的ID更新现有表的新外键列

2条答案

相关问题

热门标签

最新问答