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
WITH U AS
(SELECT COUNT (1) AS DUMMY FROM NEW TABLE
(UPDATE TABLE_2 A SET STATUS = 9 WHERE A.STATUS = 0 AND EXISTS
(SELECT 1 FROM TABLE_2 B WHERE A.ITEM_2 = B.ITEM_2 AND A.ID > B.ID AND B.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