Re: Delete and self-join

Nick Worth wrote:
>
> Hi,
>
> I have recently started porting some stuff from Oracle to PostgreSQL, and am
> having trouble with the following construct from Oracle:
>
>    DELETE  FROM serviceproviders sp1
>    WHERE exists (SELECT 1
>                  FROM serviceproviders sp2 where
>                  sp1.class = sp2.class
>                  AND sp1.userid = sp2.userid
>                  AND sp1.providerclass = oldproviderclass
>                  AND sp1.providerid = newproviderid
>                  AND sp2.providerclass = oldproviderclass
>                  AND sp2.providerid = oldproviderid);
>
> PostgreSQL returns the following error when trying to execute the aqbove
> code in a stored procedure:
>
> ERROR:  parser: parse error at or near "sp1"
>
> I have tried a number of options, but PostgreSQL doesn't seem to like sp1 in
> the DELETE clause, and if I don't have that then short of writing some code
> to select and delete in a loop I don't see how to achieve the same effect.
>
> As I am new to postgres I may be missing something obvious.
> Any help would be much appreciated.

[ Donning dumb hat ... ]

Did you try :

DELETE  FROM serviceproviders AS sp1
 ?

[ Leaving dumb hat ]
[ Donning dumber hat ]

Did you try using another name ?

[ Leaving dumber hat ]
[ Donning asbestos longjohns ]

Hope this helps !

                    Emmanuel Charpentier

--
Emmanuel Charpentier