CODEWITHSUNDEEP
sqloracle-databaseplsqlcursordml
hashir
hashir

Reputation: 197

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.

Upvotes: 2

Views: 26111

Answers (2)

Søren Kongstad
Søren Kongstad

Reputation: 1440

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

First, there is no need to store the data twice. You can just fetch it using a JOIN when you need it.

You can update the values as:

update abc
    set comp_code = (select cc.comp_code
                     from cc
                     where cc.sid1 = abc.sid1
                    );

This will update all rows in abc. If sid1 does not match in the second table, then the value will remain NULL.

EDIT:

Your second table has multiple rows with the same value. You need to figure out which one you want. To choose an arbitrary one:

update abc
    set comp_code = (select cc.comp_code
                     from cc
                     where cc.sid1 = abc.sid1 and rownum = 1
                    );

You can also choose MIN(), MAX(), LISTAGG() or some other combination of values.

Upvotes: 1

Related Questions