Thread: update set x=(subquery on same table)
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
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@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
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: > 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@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
NM, figured it out :) update nodes set parent_id=NULL where id in (select n1.id from nodes n1 left join nodes n2 on n1.parent_id=n2.id where n2.id is null); CSN --- CSN <cool_screen_name90001@yahoo.com> 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. > > Thanks, > CSN > > > --- Martijn van Oosterhout <kleptog@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 <kleptog@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 > __________________________________ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html
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