Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test (id int,WHLO  varchar(10), ITNO  varchar(10),RESP  varchar(10),SUWH varchar(10));
insert into test values
 (1,'36P','F379194','Jasmijn',null)	
,(2,'36E','F379194','Mounish','36P')
,(3,'W33','F379194','Kneza','36E')
,(4,'T44','F379194','Fatin','36E')
,(5,'32P','F379194','Mari','R55')
,(6,'R55','F379194','Oumaima',null)
;
select * from test;

CREATE TABLE



INSERT 0 6




id
whlo
itno
resp
suwh


1
36P
F379194
Jasmijn
null

2
36E
F379194
Mounish
36P

3
W33
F379194
Kneza
36E

4
T44
F379194
Fatin
36E

5
32P
F379194
Mari
R55

6
R55
F379194
Oumaima
null



SELECT 6


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, t.resp newResp
  from CTE r inner join test t on t.ITNO=r.ITNO and t.whlo=r.newWhlo
  where lvl<6
)
select * from cte order by id,lvl;


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



SELECT 10


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, t.resp newResp
  from CTE r inner join test t on t.ITNO=r.ITNO and t.whlo=r.newWhlo
  where lvl<6
)
 update test
  set resp=newresp
from  cte c 
where test.itno=c.itno and test.whlo=c.Whlo
  and newWhlo is null;

select * from test order by id;

UPDATE 4




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



SELECT 6


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, t.resp newResp
  from CTE r inner join test t on t.ITNO=r.ITNO and t.whlo=r.newWhlo
  where lvl<6
)
 update test
  set resp=newresp
--select * 
from  -- test t inner join 
  cte c where test.itno=c.itno and test.whlo=c.Whlo
  and newWhlo is null;

select * from test order by id;
-- select * from cte order by id,lvl;

UPDATE 4




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



SELECT 6


WITH recursive RecursiveUpdateCTE AS (
    SELECT id,	whlo,	itno,	resp,	suwh,
        -- T1.*, 
        CASE 
            WHEN T1.SUWH IS NOT NULL THEN T1.SUWH 
            ELSE T1.WHLO 
        END AS CurrentWHLO,
        0 AS Iteration
    FROM test T1

    UNION ALL

    SELECT id,	whlo,	itno,	resp,	suwh,
        -- 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
    and Iteration<10
)
UPDATE test
SET RESP = (
    SELECT  RUC.RESP
    FROM RecursiveUpdateCTE RUC
    WHERE RUC.ITNO = test.ITNO
    ORDER BY RUC.Iteration DESC
    limit 1
)
WHERE SUWH IS NULL;
select * from test order by id;

UPDATE 2




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
Jasmijn
null



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