Hello SQL Server Readers,
You've probably seen multiple column update examples out there using syntax such as this:
set (col1, col2) = (select col1, col2 from ...)
You've also probably found that the above syntax is not SQL Server friendly. Apparently this is Oracle syntax and very well may work on other databases too, so long as they support 'row value constructors'.
To make this work on SQL Server you'll need to make a few changes. But don't fret, when you see the example below you'll probably kick yourself like I did because it makes perfect sense and is a very familiar concept to us SQL Server developers.
From what I've read, this is what you'd call using the update statement extension. I'll just call it using an aliased subselect as that makes more sense in my mind. I never claimed to be uber technical.
One last thing before the example that you'll want to pay close attention to. In the outer where clause (if you choose to use one) be sure to qualify your column name. This may not be necessary if you're subselect does not have the same column names as your update statement. My test query did use the same column names and I couldn't get the query to run properly until I qualifed the outer where clause column. Now, without further ado, the example:
set col1 = a.col1, col2 = a.col2, col3 = a.col3 from
(select col1, col2, col3 from table2 where <expression>) as a
where table1.col1 <expression>
Good luck, and happy T-SQL.