Oracle SQL : Update multirows in oracle using subquery of same table-CodePudding

I have a table like this

Group	Item_No	UL	MODEL
10	1	40	10
10	1	35	12
10	1	30	13
10	2	NULL	10
10	2	NULL	12
10	2	NULL	13

I want to update the value of UL where ITEM_NO is 2 from UL of item no =1 minus 5 where Model is same.

So my output table should look like this

Group	Item_No	UL	MODEL
10	1	40	10
10	1	35	12
10	1	30	13
10	2	35	10
10	2	30	12
10	2	25	13

My Query looks like this :

Update table t1
   set t1.Ul = ( select t2.ul-5
                   from table t2
                  where t2.item_no =1)
 where t1.model in ( select model
                       from table t2
                      where t2.item_no = 1) , t1. Item_no =2

This query returns error as "single-row subquery returns more than one row." I tried using inner join also but was not able to do it. Can anyone help how to do it.

CodePudding user response：

I think you're looking for below:

merge into t1 x
using (select * from t1  where ITEM_NO = 1) y
on (x.M0DEL = y.M0DEL and x.ITEM_NO=2)
WHEN matched THEN
  UPDATE SET x.UL=Y.UL -5;

https://livesql.oracle.com/apex/f?p=590:43:20294207843979::NO:::

CodePudding user response：

update t1 tt1
set tt1.ul=(select tt2.ul from t1 tt2  where  tt1.model1= tt2.model1 and 
tt2.item_no=2)
where tt1.item_no=1;

CodePudding user response：

try this :

MERGE INTO table t1
USING table t2
ON (t1.model = t2.model AND t2.Item_No = 1)
WHEN MATCHED THEN
  UPDATE SET t1.UL = t2.UL - 5, t1.Item_No = 2