Update using select query with multiple Rows in Oracle

Question

Can any one please help me to solve this issue

Table Name:RW_LN

  LN_ID      RE_LN_ID     RE_PR_ID   
  LN001        RN001       RN002
  LN002        RN002       RN003
  LN003        RN003       RN001  
  LN004        RN001       RN002   

MY Update Query is:

update table RW_LN set RE_LN_ID=(
    select LN_ID
    from RW_LN as n1,RW_LN as n2
    where n1.RE_LN_ID = n2.RE_PR_ID)      

MY Expected Result is:

     LN_ID           RE_LN_ID  
     LN001            LN003  
     LN002            LN004  
     LN003            LN002  
     LN004            LN003  

This above query shows error as SUB QUERY RETURNS MULTIPLE ROWS.Can any one provide the solution for this, I am Beginner in Oracle 9i.So Stuck in the logic

Answer 1

you can try to solve this with a distinct

update table RW_LN set RE_LN_ID=(  
      select distinct LN_ID   
      from RW_LN as n1,RW_LN as n2  
      where     n1.RE_LN_ID = n2.RE_PR_ID)

if that still returns multiple rows, it means you are missing a join somewhere along the way or potentially have a bad schema that needs to use primary keys.

Answer 2

If you want to take the "biggest" corresponding LN_ID, you could do

update RW_LN r1
set r1.RE_LN_ID = (select MAX(LN_ID)
                FROM RW_LN r2
                where r1.RE_LN_ID = r2.RE_PR_ID);

see SqlFiddle

But you should explain why you choose (as new RE_LN_ID) LN004 instead of LN001 for LN_ID LN002 (cause you could choose both)

Answer 3

Just guessing, but possibly this is what you want.

update
  RW_LN n1
set
  RE_LN_ID=(  
    select n2.LN_ID   
    from   RW_LN n2  
    where  n1.RE_LN_ID = n2.RE_PR_ID)
where exists (
    select null   
    from   RW_LN n2  
    where  n1.RE_LN_ID = n2.RE_PR_ID and
           n2.ln_id is not null)

At the moment there is no correlation between the rows you are updating and the value being returned in the subquery.

The query reads as follows:

For every row in RW_LN change the value of RE_LN_ID to be:
  the value of LN_ID in a row in RW_LN for which:
    the RE_PR_ID equals the original tables value of RE_LN_ID
IF there exists at least one row in RW_LN for which:
    RE_PR_ID is the same as RE_LN_ID in the original table AND
    LN_ID is not null