Re: update empty column according to existen data

"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 Received on Sun Jul 13 2003 - 12:49:46 CEST