Re: Problem with self-join updates...

On Fri, 15 Feb 2002, Benoit Menendez wrote:

> I have the following self-join update:
>
>             update TABLE set PARENT_ID=parent.PARENT_ID
>             from TABLE, TABLE parent
>             where TABLE.PARENT_ID=parent.ID
>             and parent.ID in (1,2,3,4)
>
> This query is use to update a hierarchy before deleting specific
> records...
>
> I get the following error:
>
>     Table name "table" specified more than once
>
> This appears to be a limitation of the update syntax which is not
> documented...
>
> Is this something that will be fixed soon? or should I write this
> query differently?

The query above does a three way join of table, once for the
update table reference and once for each mention in from, which
probably isn't what you meant.

Maybe:
update table set PARENT_ID=parent.PARENT_ID
from TABLE parent
where TABLE.PARENT_ID=parent.ID
and parent.ID in (1,2,3,4)