Oracle: apply multiple updates in single statement?

Question

Suppose I have a simple update of one table with data from another table.

update t1
   set (a,b) = (select a,b from t2 where id=17)
 where id=17;

How can i apply a similar update to multiple id's in one statement? In my brain I'm thinking iteratively of something like this:

for X in (select id from t2 where ...):
    update t1
       set (a,b) = (select a,b from t2 where id=X)
     where id=X;

Generic SQL preferred, but Oracle-specific solutions welcomed as well.

Tony Andrews · Accepted Answer · 2016-01-26 17:37:15Z

1

How about:

update t1
   set (a, b) = (select a,b from t2 where t2.id=t1.id)
 where t1.id in (select id from t2 where ...);

answered Jan 26, 2016 at 17:37

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

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

1 Answer 1