Can someone help with this syntax? I have a non-sensicle example
below, but it illustrates the problem if you copy/paste into QA.

**********************************

use pubs
go

update authors set address = 'some address'
from authors a
inner join authors a2 on a.zip = a2.zip

---------------------------------------------

Server: Msg 8154, Level 16, State 1, Line 2
The table 'authors' is ambiguous.


**********************************

It needs to know which alias to update a or a2.

update a set address = 'some address'
from authors a
inner join authors a2 on a.zip = a2.zip

Jackie

<john.livermore@inginix.com> wrote in message
news:1114025028.974990.86780@o13g2000cwo.googlegro ups.com...
> Can someone help with this syntax? I have a non-sensicle example
> below, but it illustrates the problem if you copy/paste into QA.
>
> **********************************
>
> use pubs
> go
>
> update authors set address = 'some address'
> from authors a
> inner join authors a2 on a.zip = a2.zip
>
> ---------------------------------------------
>
> Server: Msg 8154, Level 16, State 1, Line 2
> The table 'authors' is ambiguous.
>
>
> **********************************
>

thx!

Of course, instead of the Microsoft proprietary syntax, you could also
write this statement with the ANSI SQL compliant syntax, as follows:

-- Note: The update is still non-sensicle...
UPDATE Authors
SET Address = (
SELECT 'some address'
FROM Authors A2
WHERE A2.zip = Authors.zip
)

HTH,
Gert-Jan


john.livermore@inginix.com wrote:
>
> Can someone help with this syntax? I have a non-sensicle example
> below, but it illustrates the problem if you copy/paste into QA.
>
> **********************************
>
> use pubs
> go
>
> update authors set address = 'some address'
> from authors a
> inner join authors a2 on a.zip = a2.zip
>
> ---------------------------------------------
>
> Server: Msg 8154, Level 16, State 1, Line 2
> The table 'authors' is ambiguous.
>
> **********************************