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.