Update rows for a given condition with select in one EXECUTION: DB2

Question

I am trying to update rows with a select on the same table. In my case, I want to update status with 0, if the value of rows occurs more than one in same column.

For example, item1 occurs three times, that's why I want to change the value of 2 another item1 with status 0.

I tried so far with my code, but I am still struggling for expected result.

select * from (
    UPDATE table_1 t1
    SET STATUS = 0 where exists (
    select t1.*, lag(ITEM, 1, 0) over (order by ITEM_1 ASC) as C2
    from table_1 AS t1) 
    WHERE ITEM_1 = C2)

#|ID| ITEM_1   |STATUS
-+--+---------+------
1|10| item1    |   1       
2|11| item1    |   1  
3|12| item1    |   1  
4| 7| item2    |   1  
5| 2| item3    |   1    
6| 9| item3    |   1     
7|13| item3    |   1      
8|14| item3    |   1    

expected result.

#|ID| ITEM_1   |STATUS
-+--+---------+------+
1|10| item1    |   1  | 
2|11| item1    |   0  | 
3|12| item1    |   0  |
4| 7| item2    |   1  |
5| 2| item3    |   1  |
6| 9| item3    |   0  |  
7|13| item3    |   0  | 
8|14| item3    |   0  | 

Answer 1

UPDATE
(
  SELECT 
    STATUS
  , ROW_NUMBER () OVER (PARTITION BY ITEM_1 ORDER BY ID) AS RN_
  FROM TABLE_1 T
)
SET STATUS = 0
WHERE RN_ <> 1;

SELECT * FROM TABLE_1 ORDER BY ITEM_1, ID;

ID	ITEM_1	STATUS
10	item1	1
11	item1	0
12	item1	0
7	item2	1
2	item3	1
9	item3	0
13	item3	0
14	item3	0

fiddle

Update

With a single statement:

WITH U AS 
(
  SELECT COUNT (1) AS DUMMY
  FROM NEW TABLE
  (
    UPDATE TABLE_1 A
    SET STATUS = 0
    WHERE EXISTS 
    (
      SELECT 1 
      FROM TABLE_1 B 
      WHERE B.ITEM_1 = A.ITEM_1 AND A.ID > B.ID
    )
  )
)
SELECT * 
FROM 
--U
TABLE_1
ORDER BY ITEM_1, ID

fiddle