Oracle SQL: Update a table with data from another table

Question

This is called a correlated update

UPDATE table1 t1
   SET (name, desc) = (SELECT t2.name, t2.desc
                         FROM table2 t2
                        WHERE t1.id = t2.id)
 WHERE EXISTS (
    SELECT 1
      FROM table2 t2
     WHERE t1.id = t2.id )

Assuming the join results in a key-preserved view, you could also

UPDATE (SELECT t1.id, 
               t1.name name1,
               t1.desc desc1,
               t2.name name2,
               t2.desc desc2
          FROM table1 t1,
               table2 t2
         WHERE t1.id = t2.id)
   SET name1 = name2,
       desc1 = desc2

Try this:

MERGE INTO table1 t1
USING
(
-- For more complicated queries you can use WITH clause here
SELECT * FROM table2
)t2
ON(t1.id = t2.id)
WHEN MATCHED THEN UPDATE SET
t1.name = t2.name,
t1.desc = t2.desc;

try

UPDATE Table1 T1 SET
T1.name = (SELECT T2.name FROM Table2 T2 WHERE T2.id = T1.id),
T1.desc = (SELECT T2.desc FROM Table2 T2 WHERE T2.id = T1.id)
WHERE T1.id IN (SELECT T2.id FROM Table2 T2 WHERE T2.id = T1.id);

Update table set column = (select...)

never worked for me since set only expects 1 value - SQL Error: ORA-01427: single-row subquery returns more than one row.

here's the solution:

BEGIN
For i in (select id, name, desc from table1) 
LOOP
Update table2 set name = i.name, desc = i.desc where id = i.id;
END LOOP;
END;

That's how exactly you run it on SQLDeveloper worksheet. They say it's slow but that's the only solution that worked for me on this case.

Here seems to be an even better answer with 'in' clause that allows for multiple keys for the join:

update fp_active set STATE='E', 
   LAST_DATE_MAJ = sysdate where (client,code) in (select (client,code) from fp_detail
  where valid = 1) ...

The full example is here: http://forums.devshed.com/oracle-development-96/how-to-update-from-two-tables-195893.html - from web archive since link was dead.

The beef is in having the columns that you want to use as the key in parentheses in the where clause before 'in' and have the select statement with the same column names in parentheses. where (column1,column2) in ( select (column1,column2) from table where "the set I want" );