467,926 Members | 1,513 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,926 developers. It's quick & easy.

Update multiple fields Accross a Join

"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.
Jul 23 '05 #1
  • viewed: 15816
Share:
4 Replies
Duane Phillips (as***@askme.askme) writes:
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?


UPDATE table_a
SET col1 = b.that_col,
col2 = b.this_col,
col3 = b.yet_another_col,
...
FROM table_a a
JOIN table_b b ON a.key_col = b.key_col

This presumes that for a row in table_a there is at most one matching
row in table_b.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
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)
Jul 23 '05 #3
Thankyou!

~ Duane Phillips.

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:9q********************************@4ax.com...
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)

Jul 23 '05 #4
Thankyou!

~ Duane Phillips.

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...
Duane Phillips (as***@askme.askme) writes:
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?


UPDATE table_a
SET col1 = b.that_col,
col2 = b.this_col,
col3 = b.yet_another_col,
...
FROM table_a a
JOIN table_b b ON a.key_col = b.key_col

This presumes that for a row in table_a there is at most one matching
row in table_b.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Melissa | last post: by
1 post views Thread by racquetballer | last post: by
13 posts views Thread by Neil | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.