update one table with data from another

SqlDatabase

Sql Problem Overview

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

Sql Solutions

Solution 1 - Sql

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

Solution 2 - Sql

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.

Solution 3 - Sql

UPDATE table1
SET 
`ID` = (SELECT table2.id FROM table2 WHERE table1.`name`=table2.`name`)

Solution 4 - Sql

Try following code. It is working for me....

UPDATE TableOne 
SET 
field1 =(SELECT TableTwo.field1 FROM TableTwo WHERE TableOne.id=TableTwo.id),
field2 =(SELECT TableTwo.field2 FROM TableTwo WHERE TableOne.id=TableTwo.id)
WHERE TableOne.id = (SELECT  TableTwo.id 
                             FROM   TableTwo 
                             WHERE  TableOne.id = TableTwo.id)

Solution 5 - Sql

Use the following block of query to update Table1 with Table2 based on ID:

UPDATE Table1, Table2 
SET Table1.DataColumn= Table2.DataColumn
where Table1.ID= Table2.ID;

This is the easiest and fastest way to tackle this problem.

Categories
Recommended Sql Solutions
Recommended Database Solutions

Previous Solution:

Dynamically switch WCF Web Service Reference URL path through config file

C#asp.netWcf

Next Solution:

XML Error: There are multiple root elements

C#Xml

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionPatrickView Question on Stackoverflow
Solution 1 - SqlThe Scrum MeisterView Answer on Stackoverflow
Solution 2 - Sqluser272735View Answer on Stackoverflow
Solution 3 - SqlAwais AfridiView Answer on Stackoverflow
Solution 4 - SqlChandramaniView Answer on Stackoverflow
Solution 5 - SqlGil BaggioView Answer on Stackoverflow