Re: update empty column according to existen data

Thank you for your reply,
However, my problem is
set EN2_ID =(select en1_id from <ur table> ty where ty.en1= tx.en2) return more than 1 row therefore I always get the error of

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

I want to set en2_id to the corresponding en1_id when en2=en1 Thank you

gspk_at_yahoo.com (Prince Kumar) wrote in message news:<629275ba.0307221601.79777b7f_at_posting.google.com>...
> 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)
> /
>
> Received on Thu Jul 24 2003 - 21:01:57 CEST