Updating one table with values from another table

0
votes

Sorry for this very basic newbie DB question...

I have two tables with the same columns but want to replace the values of one of the columns in the first table with the values of the corresponding column in the second table.

I.e. table1: name, age table2: name, age

Each table has the same "name" values, just with different ages.

Algorithmically: For each row in table1, find the row in table2 with the same name and make table1.age = table2.age

The database is Oracle. I've tried something like

update table1 set table1.age = (select table2.age where table2.name = table1.name)

thinking it might do an implicit join if needed but no luck. I've also tried explicitly doing an inner join with no luck.

Thanks!

3
oraclesql-update
Query you wrote looks perfectly OK. What does "no luck" mean? Did you get an error? Nothing happened (i.e. no rows were updated)? If possible, post a test case (by editing the question, not as a comment). - Littlefoot
Did you get something like ORA-01427 error? - Vasyl Moskalov
awaiting serious discussion about the use of common sense with storing "age" instead of a Date ....Do you really want to have to update the table with the new age when the date arrives (the DATE you never saved .. thus you get inconsistent information) - eagle275
Thanks all for your answers and comments. Turns out it was a data problem. There was a dupe in the second table which I should have realized as the error was something like "single row subquery returned multiple rows". - Max

3 Answers

3
votes

A MERGE is often faster than a scalar sub-query

merge into table1 t1
using table2 t2 on (t1.name = t2.name)
when matched then update
  set age = t2.age;
1
votes

You miss nothing but a from clause within the subquery :

update table1 t1
   set t1.age =
       (select t2.age 
          from table2 t2
         where trim(t2.name) = trim(t1.name) )

and using trim() would be good option against surrounding(leading&trailing) whitespaces for string type values.

1
votes

Try using a pl/sql loop:

begin 
for t2 in (
    select name, age
    from table2
    )
loop
    update table1 t1
    set t1.age=t2.age where t1.name = t2.name; 
end loop;
end;

Though, it is not efficient at all.