By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,361 Members | 1,352 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,361 IT Pros & Developers. It's quick & easy.

update set x=(subquery on same table)

P: n/a
CSN
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.

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

Nov 22 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
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 <kl*****@svana.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


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

iD8DBQFALIqMY5Twig3Ge+YRAmPoAJ49DzRhs6GoQa3b0HWhIm 6TvnnVqgCgmqKZ
VHnFrhGp4wXlAsa1co1zNbI=
=B2FR
-----END PGP SIGNATURE-----

Nov 22 '05 #2

P: n/a
CSN

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 <kl*****@svana.org> wrote:
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 <kl*****@svana.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

ATTACHMENT part 2 application/pgp-signature


__________________________________
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

Nov 22 '05 #3

P: n/a
On Fri, 13 Feb 2004, CSN wrote:

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.


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

Nov 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.