Issues implementing a recursive CTE that updates a column(postgres)

Question

I have an original table in this format

WHLO	ITNO	RESP	SUWH
36P	F379194	Jasmijn
36E	F379194	Mounish	36P
W33	F379194	Kneza	36E
T44	F379194	Fatin	36E
32P	F379194	Mari	R55
R55	F379194	Oumaima

What i want is something that starts with the first row checks if SUWH is null, if it is null it will move to the next row. If it is not null, it sets SUWH as CurrentWHLO and ITNO as CurrentITNO and RESP as currentRESP it then continues to look for another row with WHLO=CurrentWHLO and ITNO=CurrentITNO and repeats the process updating CurrentWHLO, CurrentITNO and CurrentRESP each iteration until it finds a line with null as SUWH value and when it does, it updates the RESP of the original line with CurrentRESP.

So the final table in the example should look something like this,

WHLO	ITNO	RESP	SUWH
36P	F379194	Jasmijn
36E	F379194	Jasmijn	36P
W33	F379194	Jasmijn	36E
T44	F379194	Jasmijn	36E
32P	F379194	Oumaima	R55
R55	F379194	Oumaima

I tried to implement a recursive function similar to this from chatgpt (I know :c) without success (It references the CTE in a weird place)

WITH RecursiveUpdateCTE AS (
    SELECT 
        T1.*, 
        CASE 
            WHEN T1.SUWH IS NOT NULL THEN T1.SUWH 
            ELSE T1.WHLO 
        END AS CurrentWHLO,
        0 AS Iteration
    FROM YourTableName T1

    UNION ALL

    SELECT 
        T2.*, 
        CASE 
            WHEN T2.SUWH IS NOT NULL THEN T2.SUWH 
            ELSE T2.WHLO 
        END AS CurrentWHLO,
        Iteration + 1
    FROM RecursiveUpdateCTE T2
    WHERE T2.SUWH IS NOT NULL
)
UPDATE YourTableName
SET RESP = (
    SELECT TOP 1 RUC.RESP
    FROM RecursiveUpdateCTE RUC
    WHERE RUC.ITNO = YourTableName.ITNO
    ORDER BY RUC.Iteration DESC
)
WHERE SUWH IS NULL;

If the logic is unclear here is some more info

We don't have to update anything on rows that have SUWH empty
on rows that have SUWH, RESP field is wrong and we have to go backwards to correct it, for example in line 2, SUWH is 36P so we have to find a row that has '36P' as WHLO and ITNO as 'F379194' (the combination is always unique) and we see if the row that we found has a SUWH. if it doesn't that's great because it is the right RESP and we update the original RESP with the RESP of this row.
If it does have SUWH, it is still a wrong RESP and we iterate again with the same logic. For example if we start at row 3, we see that SUWH is there (Wrong RESP) so we have to take 36E (SUWH) as WHLO and ITNO which would give us line 2 which also has a SUWH so it is still the wrong RESP so we again take SUWH (this time is 36P) as WHLO and ITNO which would give us line 1 and this line doesn't contain SUWH so it has the correct RESP so we have to update this correct RESP as the RESP of the original line where we started (line 3).

since i'm recently starting the sql journey and i'm kind of lost and would appreciate if you guys have anything for me.

Can you remove columns SSQT, PUIT, LEAT, SATD, MSCH from example? — ValNik
– ValNik, Commented Jan 17, 2024 at 20:54

ValNik · Accepted Answer · 2024-01-21 20:26:15Z

See example.
This is a task to find the first parent - head of the chain graph. When the value of SUWH is not null, it means that this row has a parent.

with recursive CTE as(
  SELECT id,0 lvl, whlo,    itno,   resp,   suwh
     , suwh newWhlo
     , resp newResp
  from test
  where suwh is not null
  union all
  SELECT r.id,lvl+1 lvl, r.whlo,r.itno, r.resp, r.suwh
     , t.suwh newWhlo  -- new whlo from finded(linked) row
     , t.resp newResp  -- new resp from finded(linked) row
  from CTE r inner join test t on t.ITNO=r.ITNO and t.whlo=r.newWhlo
  -- where lvl<6 -- debug only
)
update test
  set resp=newresp
from  cte c 
where test.itno=c.itno and test.whlo=c.Whlo
  and newWhlo is null;

Update query result

id	whlo	itno	resp	suwh
1	36P	F379194	Jasmijn	null
2	36E	F379194	Jasmijn	36P
3	W33	F379194	Jasmijn	36E
4	T44	F379194	Jasmijn	36E
5	32P	F379194	Oumaima	R55
6	R55	F379194	Oumaima	null

Recursive CTE output

id	lvl	whlo	itno	resp	suwh	newwhlo	newresp
2	0	36E	F379194	Mounish	36P	36P	Mounish
2	1	36E	F379194	Mounish	36P	null	Jasmijn
3	0	W33	F379194	Kneza	36E	36E	Kneza
3	1	W33	F379194	Kneza	36E	36P	Mounish
3	2	W33	F379194	Kneza	36E	null	Jasmijn
4	0	T44	F379194	Fatin	36E	36E	Fatin
4	1	T44	F379194	Fatin	36E	36P	Mounish
4	2	T44	F379194	Fatin	36E	null	Jasmijn
5	0	32P	F379194	Mari	R55	R55	Mari
5	1	32P	F379194	Mari	R55	null	Oumaima

Fiddle here

Collectives™ on Stack Overflow

Issues implementing a recursive CTE that updates a column(postgres)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related