Updating multiple columns in SQL with a bound multipart identifier

Question

I am trying to update multiple columns in an MS SQL expression using a subquery. The 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 internal expression WHEREI need a link 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 this query, I get "Multiple Part Identifier" table1.col0 "cannot be linked." Obviously, using this syntax, SQL cannot bind the current record table1 in the subquery. Now I repeat the subquery for each field and using the syntax:

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

But this causes a subquery (which is very expensive) once per column, which I would like to avoid.

?

Answer 1

sql-, from . , . .

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

CROSS APPLY sub select

UPDATE t1
SET t1.col1 = a.col1, t1.col2 = a.col2, t1.col3 = a.col3 
FROM table1 t1
CROSS APPLY
(SELECT col1, col2, col3 from table2 where table1.col0 = table2.col0) a(col1,col2,col3)

Answer 3

Or if you don't like 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>