How to update table1 field by using other table and function

Question

I have two tables and one function:

Table1 contains shop_code,batch_id

shop_code| batch_id  | regstriy_id
123 | 100  | 12
124 | 100  |13
125 | 100  |12

Table2 contains shop_code,shop_name

shop_code| shop_name  
123 | need to populate  
124 | need to populate  
125 | need to populate  

Function1 take parameter registry_id from table1 and returns shop_name

Table2 shop_name is empty I want to populate against the shop_code.

It will be great if someone can help, I am using Oracle.

I tried this but not working

 update TABLE2 set T2.SHOP_NAME = T.SHOP_NAME
 from(
 select GET_shop_name(t1.regitry_id) as shop_name ,
           t1.shop_code shop_code
           from TABLE1 T1 
                ) t where t.shop_code = t1.shop_code;

Answer 1

There are multiple ways to do it in Oracle. Right of the bat I can think about two.

1. Straightforward, not Oracle specific (assuming shop_code is unique in table1)

   update TABLE2 t2 set SHOP_NAME = 
   (
     SELECT GET_shop_name(t1.regitry_id)  
     FROM TABLE1 T1 
     WHERE t2.shop_code = t1.shop_code
   );
   

2. Oracle specific syntax(requires inline view to be key-preserved - if shop_code is not a PK column in both tables you will very likely get "SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table" )

   UPDATE
    (
       SELECT GET_shop_name(t1.regitry_id) AS new_name,
              t2.shop_code, t2.shop_name
       FROM TABLE2 t2 
       INNER JOIN t1 ON (t2.shop_code = t1.shop_code)
     )
   SET shop_name = new_name;