1

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;
  • 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? – davegreen100 Jun 3 '15 at 13:51
  • @davegreen100 I update my question please help – prashant thakre Jun 3 '15 at 13:59
  • 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 – davegreen100 Jun 3 '15 at 14:01
  • GET_shop_name is a function which takes parameter of registry_id and return shop name – prashant thakre Jun 3 '15 at 14:52
  • i may be being dumb, but where does it get shop_name from? – davegreen100 Jun 3 '15 at 14:53
3

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;
    
  • Thanks shop_code is primary key in both – prashant thakre Jun 3 '15 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.