On Thu, 20 Jan 2005 15:57:49 -0700, Duane Phillips wrote:
"David Portas" <snipped for brevity> wrote:
Example 1:
UPDATE table_a
SET col = ? /* Unspecified */
WHERE EXISTS
(SELECT *
FROM table_b
WHERE table_b.key_col = table_a.key_col)
<snip again>
Many thanks. I have used this sample extensively since you posted it. Hope
you (or someone) can help me with one more thing: How would it be written
to update several fields in table A with data from table B, where as you
have shown, a column matches the records?
TIA!
~ Duane Phillips.
Hi Duane,
The "official" (ANSI-standard) answer is:
UPDATE table_a
SET col1 = (SELECT col1
FROM table_b
WHERE table_b.key_col = table_a.key_col)
, col2 = (SELECT col2
FROM table_b
WHERE table_b.key_col = table_a.key_col)
, col3 = (SELECT col3
FROM table_b
WHERE table_b.key_col = table_a.key_col)
WHERE EXISTS (SELECT *
FROM table_b
WHERE table_b.key_col = table_a.key_col)
Unfortunately, SQL Server doesn't optimize this too well. You could also
use the proprietary T-SQL UPDATE FROM syntax:
UPDATE table_a
SET col1 = table_b.col1,
col2 = table_b.col2,
col3 = table_b.col3
FROM table_a
INNER JOIN table_b
ON table_b.key_col = table_a.key_col
The results will only be the same if each row in table a is matched by
exactly one row in table b; in other cases, the results will be different
(and the results of the UPDATE FROM syntax may even be undefined)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
