update one table with data from another

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 

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 

How can this be done for:

• SQL Server
• MySQL
• PostgreSQL
• Oracle

Answer 1

For MySql:

UPDATE table1 JOIN table2      ON table1.id = table2.id SET table1.name = table2.name,     table1.`desc` = table2.`desc` 

For Sql Server:

UPDATE   table1 SET table1.name = table2.name,     table1.[desc] = table2.[desc] FROM table1 JOIN table2     ON table1.id = table2.id 

Answer 2

Oracle 11g R2:

create table table1 (   id number,   name varchar2(10),   desc_ varchar2(10) );  create table table2 (   id number,   name varchar2(10),   desc_ varchar2(10) );  insert into table1 values(1, 'a', 'abc'); insert into table1 values(2, 'b', 'def'); insert into table1 values(3, 'c', 'ghi');  insert into table2 values(1, 'x', '123'); insert into table2 values(2, 'y', '456');  merge into table1 t1 using (select * from table2) t2 on (t1.id = t2.id) when matched then update set t1.name = t2.name, t1.desc_ = t2.desc_;  select * from table1;          ID NAME       DESC_ ---------- ---------- ----------          1 x          123          2 y          456          3 c          ghi 

See also Oracle - Update statement with inner join.