Update queries using more than one table

[Colin Spalding]

In Access, if I want to update one table with information from another,
all I need to do is to create an Update query with the two tables, link
the primary keys and reference the source table(s)/column(s) with the
destination table(s)/column(s). How do I achieve the same thing in SQL?


Regards
Colin

*** Sent via Developersdex http://www.developersdex.com ***

[Simon Hayes]

Re: Update queries using more than one table

See Example C under UPDATE in Books Online, and also "Changing Data
Using the FROM Clause".

Simon

[David Portas]

Re: Update queries using more than one table

Did you lookup the UPDATE statement in Books Online? It has several
examples:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_82n9.asp

Do note the warning about the proprietary UPDATE FROM syntax:

"The results of an UPDATE statement are undefined if the statement
includes a FROM clause that is not specified in such a way that only
one value is available for each column occurrence that is updated (in
other words, if the UPDATE statement is not deterministic). "

That should be common sense but I've seen too many people get tripped
up by this issue. The problem is that this unpredictable behaviour is
silent. No error or warning is given so a serious bug could go
undetected. Check and test your code carefully. The alternative is to
use a correlated subquery:

UPDATE Table1
SET col1 =
(SELECT Table2.col1
FROM Table2
WHERE Table2.col2 = Table1.col2);

I prefer that syntax because it always seems clearer and more logical
to me, also it is standard SQL rather than a Microsoft invention and
finally it doesn't suffer from the bug-feature just described (an error
is reported if the subquery yields more than a single value per row).
Admittedly the proprietary UPDATE FROM version is more concise in some
cases and frequently the proprietary version has the advantage on
performance.

Hope this helps.

--
David Portas
SQL Server MVP
--

[Colin Spalding]

Re: Update queries using more than one table

Thanks Simon it worked a treat.

Regards
Colin

*** Sent via Developersdex http://www.developersdex.com ***