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;

• davegreen100: i'm unclear as to what you want. Are you saying you want to update the shop_name in table2 to be the batch_id from table1, matching on shop_code?
• prashant thakre: @davegreen100 I update my question please help
• davegreen100: where is GET_SHOP_NAME getting the shop_name from? it can't be from table2 as this is what you are trying to populate
• prashant thakre: GET_shop_name is a function which takes parameter of registry_id and return shop name
• davegreen100: i may be being dumb, but where does it get shop_name from?

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;
   

• prashant thakre: Thanks shop_code is primary key in both