From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | CSN <cool_screen_name90001(at)yahoo(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: update set x=(subquery on same table) |
Date: | 2004-02-13 08:27:56 |
Message-ID: | 20040213082756.GA21256@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Feb 13, 2004 at 12:18:14AM -0800, CSN wrote:
> I'm trying to do:
>
> update nodes n1 set n1.parent_id=(select n2.id from
> nodes n2 where n2.key=n1.parent_id);
>
> To set parent_id to the id of the parent (rather than
> the key). Would UPDATE FROM fromlist work? I couldn't
> find any examples of it's use.
See the online help:
# \h update
Command: UPDATE
Description: update rows of a table
Syntax:
UPDATE [ ONLY ] table SET col = expression [, ...]
[ FROM fromlist ]
[ WHERE condition ]
So try:
update nodes set parent_id=n2.id FROM nodes n2 where n2.key=nodes.parent_id;
Unfortunatly you can't alias the table you're updating, but you can alias
the rest.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> (... have gone from d-i being barely usable even by its developers
> anywhere, to being about 20% done. Sweet. And the last 80% usually takes
> 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce
From | Date | Subject | |
---|---|---|---|
Next Message | Benjamin Jury | 2004-02-13 08:32:34 | book for postgresql |
Previous Message | CSN | 2004-02-13 08:18:14 | update set x=(subquery on same table) |