RE: Delete and self-join

Perhaps if you simply drop the outermost table alias and wrote it as:

DELETE  FROM serviceproviders
WHERE exists (SELECT 1
FROM serviceproviders sp2
where serviceproviders.class = sp2.class
AND serviceproviders.userid = sp2.userid
AND serviceproviders.providerclass = oldproviderclass
AND serviceproviders.providerid = newproviderid
AND sp2.providerclass = oldproviderclass
AND sp2.providerid = oldproviderid);

I'm guessing (and this *entrirely* a guess, as I am too lazy to actually
look), but it could be that table aliases are only valid in SELECT
statements, and therefore, Oracle is allowing a non-standard extension to
the language. The above should work though.

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From:    Nick Worth [SMTP:nick.worth@ca.semagroup.com]
Sent:    Friday, January 19, 2001 3:37 PM
To:    pgsql-general@postgresql.org
Subject:    [GENERAL] Delete and self-join

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.

Thanks,

Nick Worth

I am trying to embed this code in a stored procedure/function.