Thread: Problem with self-join updates...

On Fri, 15 Feb 2002, Benoit Menendez wrote:

> I have the following self-join update:
>
>             update TABLE set PARENT_ID=parent.PARENT_ID
>             from TABLE, TABLE parent
>             where TABLE.PARENT_ID=parent.ID
>             and parent.ID in (1,2,3,4)
>
> This query is use to update a hierarchy before deleting specific
> records...
>
> I get the following error:
>
>     Table name "table" specified more than once
>
> This appears to be a limitation of the update syntax which is not
> documented...
>
> Is this something that will be fixed soon? or should I write this
> query differently?

The query above does a three way join of table, once for the
update table reference and once for each mention in from, which
probably isn't what you meant.

Maybe:
update table set PARENT_ID=parent.PARENT_ID
from TABLE parent
where TABLE.PARENT_ID=parent.ID
and parent.ID in (1,2,3,4)

Hi,

Can anyone give me a couple of examples or a explanation of select for update is supposed to work?

I've looked in the manual and the locking stuff is confusing (or it could just be me not understanding..))and neither
ofmy SQL books go into any type of detail on locking.
 

What I want to do is select a record, do something and then update that record to reflect the outcome
of the processing with 0% chance of another process doing the same thing.  Is this possiable?

my idea:

BEGIN
SELECT * FROM table1 WHERE record_id = '290202' FOR UPDATE
-- Do processing here
UPDATE table1 SET flag1 = '11' WHERE record_id = '290202'
COMMIT

Just looking for more information....

Thanks,

GB

-- 
GB Clark II             | Roaming FreeBSD Admin
gclarkii@VSServices.COM | General Geek           CTHULU for President - Why choose the lesser of two evils?

Sorry about that last message not having a subject...

Got to turn that on!

GB

-- 
GB Clark II             | Roaming FreeBSD Admin
gclarkii@VSServices.COM | General Geek           CTHULU for President - Why choose the lesser of two evils?

SELECT .... FOR UPDATE;

locks all records returned from the SELECT. Anyone who tries to update any
one of those records will block until you commit, rollback (or the
connection is dropped and a rollback issued).

It will ensure that no-one can modifiy the record(s).

David
"GB Clark" <postgres@vsservices.com> wrote in message
news:20020215155107.1e405665.postgres@vsservices.com...
> Hi,
>
> Can anyone give me a couple of examples or a explanation of select for
update is supposed to work?
>
> I've looked in the manual and the locking stuff is confusing (or it could
just be me not understanding..))
>  and neither of my SQL books go into any type of detail on locking.
>
> What I want to do is select a record, do something and then update that
record to reflect the outcome
> of the processing with 0% chance of another process doing the same thing.
Is this possiable?
>
> my idea:
>
> BEGIN
> SELECT * FROM table1 WHERE record_id = '290202' FOR UPDATE
> -- Do processing here
> UPDATE table1 SET flag1 = '11' WHERE record_id = '290202'
> COMMIT
>
> Just looking for more information....
>
> Thanks,
>
> GB
>
> --
> GB Clark II             | Roaming FreeBSD Admin
> gclarkii@VSServices.COM | General Geek
>            CTHULU for President - Why choose the lesser of two evils?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

Hi GB,

> BEGIN
> SELECT * FROM table1 WHERE record_id = '290202' FOR UPDATE
> -- Do processing here
> UPDATE table1 SET flag1 = '11' WHERE record_id = '290202'
> COMMIT

This SQL is correct and will work as you expect.

Chris

----- Original Message -----

From: Benoit Menendez

To: pgsql-sql@postgresql.org

Sent: Friday, February 15, 2002 10:39 AM

Subject: [SQL] Problem with self-join updates...

I have the following self-join update:

 

            update TABLE set PARENT_ID=parent.PARENT_ID
            from TABLE, TABLE parent
            where TABLE.PARENT_ID=parent.ID
            and parent.ID in (1,2,3,4)

This query is use to update a hierarchy before deleting specific records...

 

I get the following error:

 

    Table name "table" specified more than once

 

This appears to be a limitation of the update syntax which is not documented...

 

Is this something that will be fixed soon? or should I write this query differently?

 

Any suggestions?

 

Thanks for your help.

 

    Benoit

Benoit Menendez <benoitm@pacbell.net> writes:
>   I have the following self-join update:

>               update TABLE set PARENT_ID=parent.PARENT_ID
>               from TABLE, TABLE parent
>               where TABLE.PARENT_ID=parent.ID
>               and parent.ID in (1,2,3,4)

>       Table name "table" specified more than once

Wasn't this answered already?  You should not have the "from TABLE"
in there.  Essentially, there's already an implicit FROM entry for
the target table, you don't need another.  "from TABLE parent"
is sufficient here.
        regards, tom lane

Thanks, this works... I did not know about the implicit table reference...

Now, I'm waiting for outer join updates and deletes so I don't have to use
in and sub-selects...

PostgreSQL rules...
   Benoit

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Benoit Menendez" <benoitm@pacbell.net>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, February 19, 2002 11:06 AM
Subject: Re: [SQL] Problem with self-join updates...


> Benoit Menendez <benoitm@pacbell.net> writes:
> >   I have the following self-join update:
>
> >               update TABLE set PARENT_ID=parent.PARENT_ID
> >               from TABLE, TABLE parent
> >               where TABLE.PARENT_ID=parent.ID
> >               and parent.ID in (1,2,3,4)
>
> >       Table name "table" specified more than once
>
> Wasn't this answered already?  You should not have the "from TABLE"
> in there.  Essentially, there's already an implicit FROM entry for
> the target table, you don't need another.  "from TABLE parent"
> is sufficient here.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html