ORACLE UPDATE multiple rows with multiple values

Question

select distinct T1.a1, T2.a1  
from T1, T2, T3 where 
T2.name='Something' and T2.value is not null and
T2.a1=T3.a1 and T1.a2=T3.a2 and 
T1.a3='Something' and T1.a4 is null

I have values populated in T2 and its missing in T1.

I have to update T1 value with T2 value. The SELECT sql is bringing values properly. But I am not able to come up with the UPDATE sql for ORACLE database.

Boneist · Accepted Answer · 2015-01-21 20:43:47Z

1

Try a MERGE:

merge into t1 tgt
using (select distinct t1.a1 t1_a1, t2.a1 t2_a1
       from   t1,
              t2,
              t3
       where  t2.name='Something' 
       and    t2.value is not null
       and    t2.a1=t3.a1
       and    t1.a2=t3.a2
       and    t1.a3='Something'
       and    t1.a4 is null) src
  on (tgt.a1 = src.t1_a1)
when matched then
update set tgt.a1 = src.t2_a1;

edited Jan 21, 2015 at 20:43

answered Jan 21, 2015 at 17:22

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

javafox Over a year ago

But where is t2.a1 is used. I dont see that in your SQL. In my SQL thats where I have the value? Am I missing something.

javafox Over a year ago

I changed this, tgt.a1 = src.t1_a1 to tgt.a1=src.t2_a1. And it worked fine. One more correction I did. In my original SQL tgt.a1 = src.t1_a1 was not primary id. So I changed it to get primary ID and afterwards it worked just fine. Thanks

Boneist Over a year ago

Yes, sorry - I made a typo. You got it right - it should have been tgt.a1 = src.t2_a1. I've corrected that now.

Collectives™ on Stack Overflow

ORACLE UPDATE multiple rows with multiple values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related