From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | "'Nick Worth'" <nick(dot)worth(at)ca(dot)semagroup(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | RE: Delete and self-join |
Date: | 2001-01-19 21:21:29 |
Message-ID: | 01C08233.E13788F0.mascarm@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)mascari(dot)com
-----Original Message-----
From: Nick Worth [SMTP:nick(dot)worth(at)ca(dot)semagroup(dot)com]
Sent: Friday, January 19, 2001 3:37 PM
To: pgsql-general(at)postgresql(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2001-01-19 21:26:32 | Re: Troubles with performances |
Previous Message | Peter Eisentraut | 2001-01-19 20:47:52 | Re: couple of general questions |