Re: update empty column according to existen data

Thank you again...
However, after running the code for a long time en2_id gets nothing. All the fields for en2_id is still empty. Please help

thanks

"Ievel" <ievel{-removeit-}_at_skynet.be> wrote in message news:<3f11bc74$0$291$ba620e4c_at_reader0.news.skynet.be>...
> "Ricky" <iqboss_at_hotmail.com> wrote in message
> news:a9c648ec.0307131020.2054965c_at_posting.google.com...
> > "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
>
> Indeed I made a small error.
> I think this version should be better.
> But I think that with 200K rows performance won't be great...
> Maybe making a small procedure using a cursor will be lot faster...
>
> UPDATE T t1
> SET en2_id = DECODE((SELECT 1
> FROM T t2
> WHERE t2.en1 = t1.en2
> AND ROWNUM < 2)
> ,1,(SELECT MIN(en1_id)
> FROM T t4
> WHERE t4.en1 = t1.en2)
> ,(SELECT MAX(t3.en1_id)+1
> FROM T t3)
> );
>
> ieVel
Received on Mon Jul 21 2003 - 18:36:09 CEST