Re: update empty column according to existen data

See whether the follwoing works for you. I haven't verified how it perfoms when there is duplicate and/or huge data.

Replace table "tx" with your table.

update tx set EN2_ID = (select en1_id from <ur table> ty where ty.en1 = tx.en2)

        where exists (select null from <ur table> ty where ty.en1 = tx.en2) /

update tx set EN2_ID =     (select r from
                                        (select rowid, 4 +
row_number()  over (order by en1_id) as r
                                        from tx
                                        where   en2_id is null) ty
                        where ty.rowid = tx.rowid
                        )
WHERE exists (select null from
                        (select rowid from tx where    en2_id is null)
ty
                        where ty.rowid = tx.rowid)

iqboss_at_hotmail.com (Ricky) wrote in message news:<a9c648ec.0307210836.6d50621f_at_posting.google.com>...
> 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 Wed Jul 23 2003 - 04:07:22 CEST