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.