Обсуждение: 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

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