Re: update empty column according to existen data

"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 Sun Jul 13 2003 - 22:09:24 CEST