Postgres Pro
Downloads
Home   >   mailing lists

Thread: Delete and self-join

Delete and self-join

From
"Nick Worth"
Date:
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.

RE: Delete and self-join

From
Mike Mascari
Date:
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.

Re: Delete and self-join

From
Stephan Szabo
Date:
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.

Re: Delete and self-join

From
Emmanuel Charpentier
Date:
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