Hi,

Is there a way in T-SQL to update several fields of a table with fields from
another table using a select statement based on a key? I remember doing that

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

[quoted text, click to view]

Hi Chip,

Is this the syntax you are looking for?

UPDATE a
SET Column1 = b.Column1,
....
ColumnN = b.ColumnN
FROM FirstTable AS a
INNER JOIN SecondTable AS b
ON b.??? = a.???
WHERE ....

Note that this syntax is proprrietary T-SQL, not ANSI-standard, and
therefore not portable to other platforms. Note also that the results may
not be as expected if a row in FirstTable matches more than one row in
SecondTable.

Best, Hugo
--