使用游标更新表，同时更新另一个表中的记录

发布于 2024-08-25 23:48:55 字数 906 浏览 6 评论 0原文

我正在使用新 ID 更新 ID，但我需要为表 A 中的主记录及其表 B 中的从属记录保留相同的 ID。

注释括起来的块是我无法弄清楚的部分。我需要更新表 B 中与我正在查看的表 A 的当前记录共享相同 ID 的所有记录。

DECLARE CURSOR_A CURSOR FOR 
SELECT * FROM TABLE_A
FOR UPDATE

OPEN CURSOR_A
FETCH NEXT FROM CURSOR_A

WHILE @@FETCH_STATUS = 0
BEGIN

BEGIN TRANSACTION
    UPDATE KEYMASTERTABLE
    SET RUNNING_NUMBER=RUNNING_NUMBER+1
    WHERE TRANSACTION_TYPE='TABLE_A_NEXT_ID'

-- FOLLOWING CHUNK IS WRONG!!!
    UPDATE TABLE_B
    SET TABLE_B_ID=(SELECT RUNNING_NUMBER
    FROM KEYMASTERTABLE WHERE TRANSACTION_TYPE='TABLE_A_NEXT_ID')
    WHERE TABLE_B_ID = (SELECT TABLE_A_ID 
    FROM CURRENT OF CURSOR A)
-- END OF BAD CHUNK

    UPDATE TABLE_A
    SET TABLE_A_ID=(SELECT RUNNING_NUMBER 
    FROM KEYMASTERTABLE WHERE TRANSACTION_TYPE='TABLE_A_NEXT_ID') 
    WHERE CURRENT OF CURSOR_A
COMMIT
    FETCH NEXT FROM CURSOR_A
END

CLOSE CURSOR_A
DEALLOCATE CURSOR_A
GO

原文

I'm updating the IDs with new IDs, but I need to retain the same ID for the master record in table A and its dependants in table B.

The chunk bracketed by comments is the part I can't figure out. I need to update all the records in table B that share the same ID with the current record I'm looking at for table A.

DECLARE CURSOR_A CURSOR FOR 
SELECT * FROM TABLE_A
FOR UPDATE

OPEN CURSOR_A
FETCH NEXT FROM CURSOR_A

WHILE @@FETCH_STATUS = 0
BEGIN

BEGIN TRANSACTION
    UPDATE KEYMASTERTABLE
    SET RUNNING_NUMBER=RUNNING_NUMBER+1
    WHERE TRANSACTION_TYPE='TABLE_A_NEXT_ID'

-- FOLLOWING CHUNK IS WRONG!!!
    UPDATE TABLE_B
    SET TABLE_B_ID=(SELECT RUNNING_NUMBER
    FROM KEYMASTERTABLE WHERE TRANSACTION_TYPE='TABLE_A_NEXT_ID')
    WHERE TABLE_B_ID = (SELECT TABLE_A_ID 
    FROM CURRENT OF CURSOR A)
-- END OF BAD CHUNK

    UPDATE TABLE_A
    SET TABLE_A_ID=(SELECT RUNNING_NUMBER 
    FROM KEYMASTERTABLE WHERE TRANSACTION_TYPE='TABLE_A_NEXT_ID') 
    WHERE CURRENT OF CURSOR_A
COMMIT
    FETCH NEXT FROM CURSOR_A
END

CLOSE CURSOR_A
DEALLOCATE CURSOR_A
GO

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

陪你到最终 2024-09-01 23:48:55

基于这样的假设：将当前数据增加 +1 的过程不会导致数据本身出现问题，我将创建一个转换表。
Column1 将是旧 ID，Column2 将是新 ID。
两个表将通过相同的更新运行。
这还可以让您对流程进行审核，以防出现问题。

像这样的东西

Update table TargetA a 
   set a.id =(select t.column2 from tranlation_table t where t.column1 = a.id);

Update table TargetB b 
   set b.id =(select t.column2 from tranlation_table t where t.column1 = b.id)

Based on the assumption that this process of incrementing current data by +1 doesn't cause issues in the data itself, I would create a translation table.
Column1 would be the old ID, Column2 would be the new ID.
Both tables would be run through the same update then.
This also gives you auditing on the process, in case something goes wrong.

Something like

Update table TargetA a 
   set a.id =(select t.column2 from tranlation_table t where t.column1 = a.id);

Update table TargetB b 
   set b.id =(select t.column2 from tranlation_table t where t.column1 = b.id)

回复收藏 0 原文

~没有更多了~