Update multiple columns in SQL with bound multi-part identifier

Question

I'm trying to update multiple columns in a MS SQL statement using a sub-query. A search led me to something like:

UPDATE table1
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>

http://geekswithblogs.net/phoenix/archive/2009/10/13/update-multiple-columns-on-sql-server.aspx

My problem is that in the inner WHERE expression I need a reference to a specific field in table1:

UPDATE table1
SET col1 = a.col1, col2 = a.col2, col3 = a.col3 FROM
(SELECT col1, col2, col3 from table2 where table1.col0 = table2.col0) AS a
WHERE table1.col1 <expression>

When I run that query I get "The multi-part identifier "table1.col0" could not be bound. ". Apparently when using that syntax SQL cannot bind the current table1 record in the subquery. Right now I am repeating the subquery for each field and using the syntax:

UPDATE table1
SET col1 = (subquery), col2 = (subquery)...

But that executes the subquery (which is very expensive) once per column, which I would like to avoid.

Any ideas?

Answer 1

in sql server, you can use a from clause in an update query. Join the tables as you would in a select. The table you are updating must be included in the joins.

update table_1
  set field_1 = table_2.value_1
  from table_1
    inner join table_2
      on (table_1.id = table_2.id)

Answer 2

Or if you dislike the join syntax this will also work:

UPDATE table1
SET col1 = a.col1, col2 = a.col2, col3 = a.col3 
FROM table1, table2 as a
WHERE table1.col0 = a.col0
AND table1.col1 <expression>