Oracle SQL Update a table with data from another table

Question

Table 1   id    name    desc ----------------------- 1     a       abc 2     b       def 3     c       adf   Table 2   id    name    desc ----------------------- 1     x       123 2     y       345   In oracle SQL  how do I run an sql update query that can update Table 1 with Table 2 s name and desc using the same id  So the end result I would get is  Table 1   id    name    desc ----------------------- 1     x       123 2     y       345 3     c       adf   Question is taken from update one table with data from another  but specifically for oracle SQL

User · Answer

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" );

User · Answer

If your table t1 and it s backup t2 have many columns  here s a compact way to do it   In addition  my related problem was that only some of the columns were modified and many rows had no edits to these columns  so I wanted to leave those alone - basically restore a subset of columns from a backup of the entire table   If you want to just restore all rows  skip the where clause   Of course the simpler way would be to delete and insert as select  but in my case I needed a solution with just updates   The trick is that when you do select   from a pair of tables with duplicate column names  the 2nd one will get named  1   So here s what I came up with     update       select   from t1 join t2 on t2 id   t1 id     where id in         select id from           select id  col1  col2      from t2         minus select id  col1  col2      from t1                   set col1 col1 1  col2 col2 1

User · Answer

BEGIN For i in  select id  name  desc from table2   LOOP Update table1 set name   i name  desc   i desc where id   i id and  name is null or desc is null   END LOOP  END

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Oracle SQL: Update a table with data from another table

Examples related to sql

Examples related to oracle

Examples related to sql-update