Oracle Certification, Database Administration, SQL, Application, Programming Reference Books
Updating multiple fields with an embedded SELECT

If you're ever faced with the task of updating multiple fields in a record, you've probably solved the task in the following way:
 
update my_table mt
set mt.field_a = (select ft.field_1
     from foreign_table ft
     where ft.field_3 = mt.field_c)
where mt.field_d = 'X';
update my_table mt
set mt.field_b = (select ft.field_2
      from foreign_table ft
      where ft.field_3 = mt.field_c)
where mt.field_d = 'X';

This method is good.  It's specific as to what needs to be done, and it's easy to debug.  Granted.  This is the tried and true method, but there is a better way.   Here's how I would do it:
 
update my_table mt
set (mt.field_a, mt.field_b) = (select ft.field_1, ft.field_2
     from foreign_table ft
     where ft.field_3 = mt.field_c)
where mt.field_d = 'X';

Now see!  Isn't this method much better.  I had no idea that SQL could do this until I saw a friend of mine do this in one of his programs.  Here, you're specifying in a row what fields you want updated, and then using a multi-column SELECT to provide those values.  However, I must admit what's "convenient" here is the fact that both the UPDATE and the embedded SELECT criteria are the same in the red method shown above.  We could then use that convenience to build a statement that updates multiple columns at a time.  So, you may not always encounter a situation where this convenience occurs, but it's here if you need it.

Return to : Oracle Database, SQL Tips