Search code examples
sqloracle-databaseplsqlcursordml

Update a table with values from another table

I have a table (say,ABC) which has the following structure :

 COMP_CODE    NAME    SALARY  SID1
-------------------------------------
  NULL      Alex     42000     85
  NULL      Alex     42000     89
  NULL      Alex     42000     96
  NULL      Alex     42000    100
  NULL      Alex     42000     52

I want to update the _COMP_CODE_ column in the above table from value residing in another table (CC). The matching columns among the two table is SID1. The structure of CC is as follows :

  COMP_CODE    SID1
----------------------
    0AA         85
    0AB         96
    0CD         98
    0DE         72
    0EH        100

Can this achieved through a CURSOR or any other sort of method.

Solution 
  • MERGE INTO ABC t1
USING (select SID,max(COMP_CODE) COMP_CODE from CC GROUP BY SID) t2
ON (t1.SID1= t2.SID1)
WHEN MATCHED THEN 
UPDATE SET t1.COMP_CODE    = t2.COMP_CODE