Update table using cursor but also update records in another table

Question

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 dependents 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

Answer 1

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)