Re: update set x=(subquery on same table)

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