use same table more than once in update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

use same table more than once in update

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2004-02-26 : 11:53:00

Here's an update query


UPDATE AAB.dbo.Absences SET FreeGuidField_01 = doel.Id 
FROM AAB.dbo.Absences a
JOIN AAB.dbo.Absences a1 ON a.Freeguidfield_01 = a1.id
JOIN ESIBS.dbo.tmpDubRef verwijder ON verwijder.HID = a1.HId AND verwijder.Verwijderen = 1
JOIN ESIBS.dbo.tmpDubRef blijf ON verwijder.Referentie = blijf.Referentie AND blijf.Verwijderen = 0
JOIN AAB.dbo.Absences doel ON blijf.Hid = doel.Hid
WHERE verwijder.referentie = '32446'

I get this error:
Server: Msg 8154, Level 16, State 1, Line 1
The table AAB.dbo.Absences' is ambiguous.

Why can I not use the target table for the update multiple times? And how do I work around that?

Henri

~~~
Success is the ability to go from one failure to another with no loss of enthusiasm

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2004-02-26 : 12:00:05

After reading the BOL carefully I found it:

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.

I just had to 'unalias' one of the tables...

Henri

~~~
Success is the ability to go from one failure to another with no loss of enthusiasm

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-26 : 12:01:08

I haven't tried this but:

UPDATE a

SET a.FreeGuidField_01 = doel.Id

FROM AAB.dbo.Absences a
JOIN AAB.dbo.Absences a1 ON a.Freeguidfield_01 = a1.id
JOIN ESIBS.dbo.tmpDubRef verwijder ON verwijder.HID = a1.HId AND verwijder.Verwijderen = 1
JOIN ESIBS.dbo.tmpDubRef blijf ON verwijder.Referentie = blijf.Referentie AND blijf.Verwijderen = 0
JOIN AAB.dbo.Absences doel ON blijf.Hid = doel.Hid
WHERE verwijder.referentie = '32446'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources