UPDATE (?)

Question

UPDATE (?)

Hans Lindgren

SSChampion

Points: 10496
More actions
August 29, 2003 at 6:35 am

#81950

Hello all!
As I was sitting down infront of my usual computer, deciding to do some work (for once), I came upon a BOL (SP3) documentation error or undocumented extension of the T-SQL language.
Happily I was coding my usual UPDATE statements and once again I had to do my autojoin. Reading in BOL it states
quote:
Syntax
UPDATE
{
table_name WITH ( < table_hint_limited > [ ...n ] ) (...)
Set to be precise as I was/am continuing with BOL:
quote:
Arguments
table_name
Is the name of the table to update. The name can be qualified with the linked server, database, and owner name if the table is not in the current server or database, or is not owned by the current user.
WITH ( < table_hint_limited > [ ...n ] ) (...)
Oki. But if I do my autojoins?
quote:
FROM < table_source >
Specifies that a table is used to provide the criteria for the update operation. For more information, see FROM.
table_name [ [ AS ] table_alias ]
Is the name of a table to provide criteria for the update operation.
If the table being updated is the same as the table in the FROM clause, and there is only one reference to the table in the FROM clause, table_alias may or may not be specified. If the table being updated appears more than one time in the FROM clause, one (and only one) reference to the table must not specify a table alias. All other references to the table in the FROM clause must include a table alias.
Test data:
SELECT 1 AS col1, 'One' AS col2 INTO tab1
INSERT INTO tab1 SELECT 2 AS col1, 'Two' AS col2
UPDATE alias1
SET col1 = alias2.col1
FROM tab1 AS alias1
JOIN
tab1 AS alias2
ON alias1.col1 = alias2.col1
Can anyone explain to me why this works correctly?
Is it some good-hearted MS developer that has decided to give us something more useful without telling his boss?
Regards, Hans!
P.S I have no table called alias1
Edited by - hanslindgren on 08/29/2003 06:37:25 AM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 1

Check BOL 2000: UPDATE (Level 4)

The 6.x example may give a hint why it was changed. I think the update can be a bit ambigous to resolve. Example one can specify different criteria per table. Which one of the sets will be updated a1 or a2.

Tested your example on 8.00.194.

ckempste SSCoach Points: 17983 More actions · Answer 2

Hi there, here are some examples of the alternatives, the 1st is the alias example..

UPDATE MYTABLEALIAS

SET col1 = 100

FROM dbo.cktest1 tab1

INNER JOIN dbo.cktest2 MYTABLEALIAS ON tab1.id = MYTABLEALIAS.id

OR THIS

UPDATE dbo.cktest2 SET col1 = 100 FROM dbo.cktest1 tab1 where tab1.id = cktest2.id

OR THIS

UPDATE dbo.cktest2 SET col1 = 100 where exists (select 'x' from dbo.cktest1 as tab1 where tab1.id = cktest2.id)

OR THIS

UPDATE dbo.cktest2 SET col1 = 100 from dbo.cktest2 MYTABLEALIAS, dbo.cktest1 tab1 where tab1.id = MYTABLEALIAS.id

Chris Kempster

http://www.chriskempster.com

Author of "SQL Server 2k for the Oracle DBA"

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

Hans Lindgren SSChampion Points: 10496 More actions · Answer 3

5409045121009:

You are right. But it is NOT written in the specifications for the Update command. It say that if you reference the same table more then once, you cannot alias one of them. It does NOT write anything about the possibilities for using the alias in the update statement, just the table_name. You shouldn't be expected to read the 6.x -> 2000 Change documentation to know how to correctly use SQL2k, right?

Regards, Hans!

Edited by - hanslindgren on 09/02/2003 02:59:52 AM

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 4

I agree. I still wish one can get a SQL printed manual like with 6.5, I think it was the 'Programmers Reference manual', which you can take home and study front to back!