Re: Updating several fields in a table with a select statement

From: Anith Sen (anith_at_bizdatasolutions.com)
Date: 11/29/04 

Date: Mon, 29 Nov 2004 15:48:20 -0600

Not sure if you are asking about the ROW CONSTRUCTOR form, which is not
supported in t-SQL yet. However, in general, you can use a simple UPDATE
like:

UPDATE tbl
   SET col11 = ( SELECT t.col1 FROM t WHERE t.key = tbl.key ),
       col12 = ( SELECT t.col2 FROM t WHERE t.key = tbl.key ),
       ...
       col1n = ( SELECT t.coln FROM t WHERE t.key = tbl.key )
 WHERE EXISTS ( SELECT * FROM t WHERE t.key = tbl.key )

Unless you are using non-key columns in your joining clause, you can use the
t-SQL FROM clause directly like:

UPDATE tbl
   SET col1 = t.col1,
       col2 = t.col2,
       ...
       coln = t.coln
  FROM t
 WHERE t.key = tbl.key ; 

-- 
Anith