CODEWITHSUNDEEP
sqloracle-databasesql-update
Phar0x
Phar0x

Reputation: 147

SQL update values in one table to match values of another table in Oracle SQL Developer

I have two tables t1 and t2:

  t1                       t2
 -------------            ------------- 
  NAME | CODE              NAME | CODE
 -------------            -------------
   ABC | 1651               KKK |  127 
   CDE |  127               KKK |  127 
  SKND |  127               AAA |   66 
                            BBB | 1651

I want to update values in column name in t1 to match values in column name in t2.

In t1, code values can have different name values, but in t2 one code has always the same name, but there are more rows with the same code because t2 has other columns which differ.

I tried something like this: 

UPDATE t1
SET t1.NAME = 
  (
    select t2.NAME 
    from t2
    inner join t1
    on t1.CODE = cast(t2.CODE as varchar2(128))
  ) 
where t1.CODE in ( select t2.CODE from t2 );

Problem is that the select to set t1.NAME wont return only 1 row because neither code in t1 or t2 is unique.

I've been looking all over but all the questions had only unique values in the table they wanted values from.

I think I'm still far from solution, I'm quite new to SQL.

Basically I want t1 to look like this:

-------------
 NAME | CODE
-------------
  BBB | 1651
  KKK | 127
  KKK | 127

I use Oracle SQL Developer, so I can't use Select directly inside Update.

Upvotes: 0

Views: 1376

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

I think you want a correlated subquery:

UPDATE t1
    SET t1.NAME = (select t2.NAME 
                   from t2
                   where t1.CODE = cast(t2.CODE as varchar2(128))
                  ) 
    where t1.CODE in (select t2.CODE from t2);

If you still have duplicate problems, you can either add and rownum = 1 to the subquery or make its select something like select max(t2.name).

Note: You should probably just have a proper foreign key relationship between the tables and use a join to get the information when you query the tables, rather than updating the values.

Upvotes: 4

Related Questions