update set x=(subquery on same table)

[CSN]

I'm trying to do:

update nodes n1 set n1.parent_id=(s elect n2.id from
nodes n2 where n2.key=n1.paren t_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.

TIA,
CSN



_______________ _______________ ____
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.

http://taxes.yahoo.com/filing.html

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Martijn van Oosterhout]

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

On Fri, Feb 13, 2004 at 12:18:14AM -0800, CSN wrote:[color=blue]
> I'm trying to do:
>
> update nodes n1 set n1.parent_id=(s elect n2.id from
> nodes n2 where n2.key=n1.paren t_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.[/color]

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.pa rent_id;

Unfortunatly you can't alias the table you're updating, but you can alias
the rest.
--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> (... 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[/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFALIqMY5T wig3Ge+YRAmPoAJ 49DzRhs6GoQa3b0 HWhIm6TvnnVqgCg mqKZ
VHnFrhGp4wXlAsa 1co1zNbI=
=B2FR
-----END PGP SIGNATURE-----

[CSN]

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


That does the trick. I'd also like to figure out a way
to set all parent_ids to NULL if no parent row can be
found. I haven't been able to figure it out so far.

Thanks,
CSN


--- Martijn van Oosterhout <kleptog@svana. org> wrote:[color=blue]
> On Fri, Feb 13, 2004 at 12:18:14AM -0800, CSN wrote:[color=green]
> > I'm trying to do:
> >
> > update nodes n1 set n1.parent_id=(s elect n2.id[/color]
> from[color=green]
> > nodes n2 where n2.key=n1.paren t_id);
> >
> > To set parent_id to the id of the parent (rather[/color]
> than[color=green]
> > the key). Would UPDATE FROM fromlist work? I[/color]
> couldn't[color=green]
> > find any examples of it's use.[/color]
>
> 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.pa rent_id;
>
> Unfortunatly you can't alias the table you're
> updating, but you can alias
> the rest.
> --
> Martijn van Oosterhout <kleptog@svana. org>
> http://svana.org/kleptog/[color=green]
> > (... have gone from d-i being barely usable even[/color]
> by its developers[color=green]
> > anywhere, to being about 20% done. Sweet. And the[/color]
> last 80% usually takes[color=green]
> > 20% of the time, too, right?) -- Anthony Towns,[/color]
> debian-devel-announce
>[/color]
[color=blue]
> ATTACHMENT part 2 application/pgp-signature[/color]



_______________ _______________ ____
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.

http://taxes.yahoo.com/filing.html

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[scott.marlowe]

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

On Fri, 13 Feb 2004, CSN wrote:
[color=blue]
>
> That does the trick. I'd also like to figure out a way
> to set all parent_ids to NULL if no parent row can be
> found. I haven't been able to figure it out so far.[/color]

could a circular foreign key reference with one delete set null type thing
work? then it'd be automagic


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org