Update values in one table with values in another table

Question

I have table1 and table2. They have the same columns and the column ID is the one that i can use to connect the tables.

How can i run foreach statment that will update row Name in table1 with the value for column Name in table2? I need this so i can fix the column Name in Table1 because it is incorect , and the good values for it are in table2

I tried using a single update statement but it takes forever to execute because both tables are with over 600 000 rows

 update 
  table1 t1
set
  (
    t1.name
      ) = (
    select
      t2.name
    from
      table2  t2
    where
      t2.id = t1.id
    and
      rownum = 1    
     )
    where exists (
      select 
        null
      from 
        table2 t2
      where 
        t2.id = t1.id
      );

I tried using one single update, but these tables are with over 600000rows and it takes forever to execute update table1 t1 set ( t1.name ) = ( select t2.name from table2 t2 where t2.id = t1.id and rownum = 1 ) where exists ( select null from table2 t2 where t2.id = t1.id ); — Сашко Мицевски, Feb 07 '18 at 22:04
Have you tried Google??? This link is the first result that came out in Google. https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle — Eric, Feb 07 '18 at 22:09

score 1 · Accepted Answer · answered Feb 07 '18 at 22:53

For this query:

update table1 t1
    set t1.name = (select t2.name from table2  t2 where t2.id = t1.id and  rownum = 1)
    where exists (select 1
                  from table2 t2
                  where t2.id = t1.id
                 );

You want an index on table2(id, name).

score -1 · Answer 2 · answered Feb 07 '18 at 22:20

-1

A simple inner join should work this.

UPDATE T1
SET T1.NAME = T2.NAME 
FROM MyTable T1
INNER JOIN MyOtherTable T2 
ON T1.ID = T2.ID

answered Feb 07 '18 at 22:20

Vidmantas Blazevicius

4,652
2
11
30

Update values in one table with values in another table

2 Answers2