Inner join and update the table in one execution DB2

January 4, 2023

I have two tables, where I would like to update table_2 if the column’s value is same and then applying inner join with table1. I would like to do in one execution.

Here I habe table1 and table2, where item_2 of table2 has same value with status = 0. Here I would like to update one of the status with 9.

table1
#|ID| ITEM_1   |Application
-+--+----------+------
1|1| item1     |   read       
2|2| item1     |   write
3|3| item1     |   learn

table2
#|ID| ITEM_2   |Description  |STATUS
-+--+---------+---------------------
1|10| item1    |   des1      | 0    
2|11| item1    |   des2      | 0
3|12| item1    |   des3      | 2

For updating table2, I used lag() function and then inner join with table1.

But here I need to execute two times, first for update and then second for inner join. But I am looking to execute in one time.

update

UPDATE
(
  SELECT 
    T2.*
  , lag(ITEM_2, 1, 0) over (order by ITEM_2 ASC) as C2
  FROM TABLE_2 T2 where T2.STATUS = 0
)
SET STATUS = 9
WHERE C2 = ITEM_2;


#|ID| ITEM_2   |Description  |STATUS
-+--+---------+---------------------
1|10| item1    |   des1      | 0    
2|11| item1    |   des2      | 9
3|12| item1    |   des3      | 2

inner join


select T1.ID, T1.ITEM_1, T1.Appliction, T2.ID, T2.ITEM_2, T2.Description, T2.STATUS
from TABLE_1 T1
INNER JOIN TABLE_2 T2 ON  T1.ITEM_1 = T2.ITEM_2
where T2.STATUS = 0

ID  | ITEM_1      | APPLICTION  | ID    | ITEM_2      | DESCRIPTION    | STATUS
1   | item1       | read        | 10    | item1       | des1           | 0

>Solution :

WITH U AS 
(SELECT COUNT (1) AS DUMMY FROM NEW TABLE
  (UPDATE TABLE_2 A SET STATUS = 9 WHERE EXISTS 
    (SELECT 1 FROM TABLE_2 B WHERE B.ITEM_2 = A.ITEM_2 AND A.ID > B.ID AND A.STATUS = 0
    )))
select T1.ID, T1.ITEM_1, T1.Appliction, T2.ID, T2.ITEM_2, T2.Description, T2.STATUS 
  from TABLE_1 T1
Inner join TABLE_2 T2 ON  T1.ITEM_1 = T2.ITEM_2
where T2.STATUS = 0`

fiddle