Update a table with values from another table

Question

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 1

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  

Solution 2

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.

Comments

[MKR]

You can use direct query to update ABC table. Why to use cursor?

[Ponder Stibbons]

It's easy when there is one comp_code for each sid1. But which do You want for 96? Minimum, maximum, first unused? Please explain and show expected output.

[hashir]

@ManojKumarRai I want to update the whole set of data in a single go, that's why!

[hashir]

@PonderStibbons I have edited the data of the CC table. Please have a look and provide some solution to the problem if possible.

[hashir]

ORA-30926: unable to get a stable set of rows in the source tables

[hashir]

Smooth man. One hell of a solution. Great work! Thanks for help

[XING]

@hashir. Merge would had been a better solution than this one. The problem was just your data not the statement. See updated anwer.

[hashir]

@XING This updates a single row only when i run it. No offence but the data is what it is.

[Søren Kongstad]

Oops I didn't see you had multiple values in CC. That's what gives the error. You have to choose which value to use. I've edited the query to give an option.

[hashir]

@GordonLinoff I think I made a hasty decision in choosing the correct answer for the question. After a bit of pondering over solution through the MERGE technique, I can say that it is way more sensible and meaningful. Appreciate everyone's effort though.

[hashir]

@@SørenKongstad Thank You. This makes so much more sense.