Updating table recursively

user_1067851 0
Viewed 53

I'm trying to update a table with values from the same table but it's failing because it seems the order of the update is important.

The idea is to set the P0 and P1 values with the parents ID instead of null for rows 2 and 3.

CREATE TABLE #Test
    (ID varchar(20),
    ParentId varchar(20),
    P0 varchar(20),
    P1 varchar(20),
    P2 varchar(20),
)

INSERT INTO #Test values ('100000','HEAD','100000', null, null);
INSERT INTO #Test values ('100100','100000',null, '100100', null);
INSERT INTO #Test values ('100200','100100',null, null, '100200');  

UPDATE U SET U.P1 = S.P1 FROM #Test U INNER JOIN #Test S ON S.ID = U.ParentID WHERE U.P2 IS NOT NULL
UPDATE U SET U.P0 = S.P0 FROM #Test U INNER JOIN #Test S ON S.ID = U.ParentId WHERE U.P1 IS NOT NULL 
    
SELECT * FROM #Test
DROP table #Test

If I run the above the P0 value in row 3 is not updated unless I run the last statement again. I'm guessing it's because when it tries to update 100200, 100100 doesn't have a value yet, it then does 100100 and updates it from 100000. Any way around this? The actual table and data are 7 levels deep.

tsql sql-server sql
1 Answers
WHILE (SELECT count(*) FROM #Test WHERE (P0 IS NULL OR P1 IS NULL) AND ParentId<>'HEAD')  > 0
BEGIN  
 UPDATE U SET U.P0 = S.P0 FROM #Test U INNER JOIN #Test S ON S.ID = U.ParentId WHERE U.P1 IS NOT NULL; 
 UPDATE U SET U.P1 = S.P1 FROM #Test U INNER JOIN #Test S ON S.ID = U.ParentID WHERE U.P2 IS NOT NULL;
END;

EDITED: As an answer to comments I propose to improve my solution as follows:

WHILE (SELECT count(*) FROM Test202011 WHERE (P0 IS NULL OR P1 IS NULL) AND ParentId<>'HEAD')  > 0
    BEGIN  
        UPDATE TOP(1000) U SET U.P0 = S.P0 FROM Test202011 U INNER JOIN Test202011 S ON S.ID = U.ParentId WHERE U.P1 IS NOT NULL; 
        UPDATE TOP(1000) U SET U.P1 = S.P1 FROM Test202011 U INNER JOIN Test202011 S ON S.ID = U.ParentID WHERE U.P2 IS NOT NULL;
        WAITFOR DELAY '00:00:01';
    END;

I am not sure that delay operator is necessary, professional comments/advise are welcome!

user_143862160
Related