By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,410 Members | 1,626 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,410 IT Pros & Developers. It's quick & easy.

Update multiple fields Accross a Join

P: n/a
"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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.