Re: update empty column according to existen data

"Ievel" <ievel{-removeit-}_at_skynet.be> wrote in message news:<3f113957$0$288$ba620e4c_at_reader1.news.skynet.be>...
> "Ricky" <iqboss_at_hotmail.com> wrote in message
> news:a9c648ec.0307111115.14fe5ef0_at_posting.google.com...
> > HI, I have a question given that I have a table T
> >
> > en1_id | en1 | en2_id | en2
> > 1 A (empty) B
> > 2 B (empty) C
> > 3 C (empty) D
> >
> > eventually I want to update en2_id according to en1 which mean if
> > en2=en1 set en2_id to en1_id. If en2 is not in en1 then create a new
> > id for en2_id
> >
> > the end table looks like
> > en1_id | en1 | en2_id | en2
> > 1 A 2 B
> > 2 B 3 C
> > 3 C 4 D
> >
> > I first try to build a view
> > >create view VT as select * from T;
> >
> > then I do update
> > >update T
> > set en2_id=en1_id
> > where T.en2 in(select VT.en1 from VT);
> >
> > but this give me something like
> > 1 A 1 B
> > 2 B 2 C
> > 3 C e D
> > It just set en2_id=en1_id on the same row instead of setting it to the
> > corresponding row.
> >
> > is there any way to update the table to the way I wanted
> >
> > Thanks alot !!!
> >
> > Ricky
>
> I haven't been able to test the following and you probably can find more
> performant statements, but it think it does the job:
>
> UPDATE T t1
> SET en2_id = DECODE(SELECT 1
> FROM T t2
> WHERE t2.en1 = t1.en2
> ,1,(SELECT en1_id
> FROM T t4
> WHERE t4.en1 = t1.en2)
> ,(SELECT MAX(t3.en1_id)+1
> FROM T t3)
> );
>
> ieVel

Thank you for your reply,
However, when I put in your query I got error

SQL> update en10p t1
  2 set en2_id=DECODE((select 1 from en10p t2   3 where t2.en1=t1.en2)
  4 ,1,(select en1_id from en10p t4
  5 where t4.en1=t1.en2)
  6 ,(select max(t3.en1_id)+1
  7 from en10p t3)
  8 );

ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

Forgot to mention that the example I given is just an example the realy table has more than 200K entries thus it might return more than 1 row in the subquery. Is there any way to over come this.

Thanks Received on Sun Jul 13 2003 - 21:20:11 CEST