0

I need to use join in below instead of Subquery. can anybody help me to rewrite this with JOIN.

    update Table1
    set status = 'Edited' 
    where val_74 ='1' and status ='Valid'
    and val_35 is not null
    and (val_35,network_id) in 
    (select val_35,network_id from
    Table2 where val_35 is not null
    and status='Correct_1');



    update Table1 b SET (Val_12,Val_13,Val_14)=
    (select Val_12,Val_13,Val_14 from
      (select Val_35,network_id, Val_12, Val_13, Val_14
      from Table2
      where  Val_34 is not null
      and (REGEXP_LIKE(Val_13,'^[0-9]+$'))
      and (Val_14 is null or (REGEXP_LIKE(Val_14,'^[0-9]+$')))
      group by Val_35,network_id,Val_12,Val_13,Val_14
      )
      where  Val_35 = b.Val_35 and network_id = b.network_id and rownum=1
    )
    where status = 'PCStep2' and (regexp_like(Val_13,'[MSS]+') or regexp_like(Val_14,'[MSS]+'));

I tried a lot with my less Knowledge In SQL JOINs. but getting multiple erros. can anybody help me with the queries at the earliest.

Hearty thanks in advance

Shabeeralimsn
  • 797
  • 4
  • 11
  • 32
  • 2
    both update statement giving error? if so post the error also – Sachu Jul 07 '15 at 05:08
  • 1
    Also please show us what you tried (even if it's not working)... we are much more likely to figure out what you need if you shows us that. Also - it'd be good if you could give us some sample data and sample output (if it was working). Note: don't post this in comments - edit your question and add it there (code formatting is awful in comments and this should be part of your original question) – Taryn East Jul 07 '15 at 05:11
  • I am getting this error for the first query,SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table 01779. 00000 - "cannot modify a column which maps to a non key-preserved table" – Shabeeralimsn Jul 07 '15 at 06:26
  • 1
    You want to update Table1 rows with a join update. In this case Oracle needs to be sure that the join of Table1 and Table2 preserve a 1-1 relation with Table1. This means that for each row of Table1 the join can generate 0 or 1 row, but never more than 1 (many rows). The only way Oracle has to ensure this is checking if the join condition contains the primary/a unique key of Table2. And Oracle is saying to you that there is not a primary/unique key preserving the join/mapping, this means (val_35,network_id) is not or it does not contain the primary/a unique key of Table2. – acesargl Jul 07 '15 at 08:22

1 Answers1

0

Actually you can not mix a update statement with a join statement. An update statement always expects exactly one table definition after the update command.

-- ORA-00971: missing SET keyword
update orders o, customers c
set o.order_value = c.account_value
where o.cust_id = c.cust_id

-- works fine
update orders o
set o.order_value = (select c.account_value  
                     from customers c
                     where c.id = o.cust_id)
ridi
  • 162
  • 9