Re: Delete and self-join

You don't need the alias on the table name.  I'm assuming
oldprividerclass and such are parameters into the function...
You should be able to replace sp1 with serviceproviders in the
subselect.

On Fri, 19 Jan 2001, 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.