When updating a table, if you need to associate it with other tables, such SQL statements are hard to implement in Oracle. Each where condition must be nested with a bunch of select statements ~~
The following SQL statement is written at work. Make a Memorandum:
-- Update Net accounts receivable
Update T_0303009 T1
Set T1.item _ value =
(
Select T2.item _ value
From
(
Select A. product_id, A. item_id, B. item_value
From ( Select Product_id, item_id, item_value
From T_0303009
Where Item_id Like ' B % 011 ' ),
( Select Product_id, item_id, item_value
From T_0303009
Where Item_id Like ' B % 008 ' ) B
Where A. product_id = B. product_id
And Substr (A. item_id, 2 , 6 ) = Substr (B. item_id, 2 , 6 )
And A. item_value = 0
) T2
Where T1.product _ id = T2.product _ id
And T1.item _ id = T2.item _ id
)
Where T1.product _ id = ( Select Product_id From
(
Select A. product_id, A. item_id, B. item_value
From ( Select Product_id, item_id, item_value
From T_0303009
Where Item_id Like ' B % 011 ' ),
( Select Product_id, item_id, item_value
From T_0303009
Where Item_id Like ' B % 008 ' ) B
Where A. product_id = B. product_id
And Substr (A. item_id, 2 , 6 ) = Substr (B. item_id, 2 , 6 )
And A. item_value = 0
) Jj
Where T1.product _ id = JJ. product_id
And T1.item _ id = JJ. item_id
)
And T1.item _ id = ( Select Item_id From
(
Select A. product_id, A. item_id, B. item_value
From ( Select Product_id, item_id, item_value
From T_0303009
Where Item_id Like ' B % 011 ' ),
( Select Product_id, item_id, item_value
From T_0303009
Where Item_id Like ' B % 008 ' ) B
Where A. product_id = B. product_id
And Substr (A. item_id, 2 , 6 ) = Substr (B. item_id, 2 , 6 )
And A. item_value = 0
) Jj2
Where T1.product _ id = Jj2.product _ id
And T1.item _ id = Jj2.item _ id
)