CODEWITHSUNDEEP
sqlsql-server
solkim
solkim

Reputation: 99

How to bulk update a table with recursive data

I have created a temporary table where I have duplicated rows with new id's in the same table. All have parent id's that has to be updated according to the old id's

A created temporary table has the fields (new_id, new_parentid, old_id, old_parentid) the new_id, old_id and old_parentid are inserted by a bulk insert.

Does anybody have an idea about how to update the New_Parent_Id so it will have the same structure as the old? 

declare @STARTNODE int

declare @tempTable table(New_Id int, New_Parent_Id int, Old_Id int, Old_Parent_Id int)

INSERT ORIGTABLE(OLD_GROUPID,OLD_GROUPPARENTID)
OUTPUT inserted.New_Id, inserted.Old_Id, inserted.Old_Parent_Id 
into @tempTable(New_Id,Old_Id,Old_Parent_Id)
SELECT a.ID, a.PARENT_ID FROM SHOP_ORIGTABLE a 
WHERE a.ID IN (SELECT ID FROM fn_allGroups(@STARTNODE))

where the function fn_allGroups gets all fields from a hierarchy.

Upvotes: 2

Views: 880

Answers (1)

BertuPG
BertuPG

Reputation: 653

if I correclty understood your table's structure, after the insert on @tempTable you have to run this update:

UPDATE a
SET a.new_parent_id = b.new_id
FROM @tempTable a
    INNER JOIN @tempTable b ON b.old_id = a.old_parent_id

Upvotes: 4

Related Questions