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

score 82 · Accepted Answer · answered Feb 18 '11 at 02:02

82

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

answered Feb 18 '11 at 02:02

The Scrum Meister

29,113
8
64
63

2

+1. In SQL Server I usually use the same structured update as the one you've shown. But in this particular case the `FROM` part could be a bit simpler: just `FROM table2 WHERE table1.id = table2.id`, and the aliases on the left side of each assignment could be dropped. – Andriy M Feb 18 '11 at 09:01
1

@andriy-m i prefer putting the join predicate in the `JOIN` and not in the `WHERE`... – The Scrum Meister Feb 18 '11 at 09:18
1

Can you update for Oracle, perhaps? Maybe confirm either of those 2 statements would work for Oracle? – p.campbell Aug 11 '11 at 18:16
@p-campbell See http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join – The Scrum Meister Aug 11 '11 at 18:23
I'll like to add: for Postgresql, the syntax is slightly different `update a set field1 = concat_ws(' ',field1, table2.middle_name, table2.first_name) from table2 where a.id = table2.fieldX;` Just added some funtions to show what is possible. – Peter Sep 18 '13 at 16:14
Hi everyone, I've tried this kind of SQL query for my updates in my table but I always get this error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, , >= or when the subquery is used as an expression." Can anyone tell me how to fix this? – user3312649 Nov 29 '17 at 07:07
@user3312649 Please post a new question. (Try adding a LIMIT 1 to the end of the subquery) – The Scrum Meister Nov 29 '17 at 18:01
Here is the my problem thanks. https://stackoverflow.com/questions/47585429/sql-merge-and-update-statemens – user3312649 Dec 01 '17 at 01:44

score 24 · Answer 2 · edited May 23 '17 at 12:34

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.

"when matched then update set". Looks like you can also tell oracle to "also order some fries while you're at it" XD — Cybermonk, Sep 11 '20 at 10:19

score 8 · Answer 3 · edited Nov 28 '16 at 14:25

8

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

edited Nov 28 '16 at 14:25

Koraktor

38,541
9
69
98

answered Jan 13 '16 at 11:41

Awais Afridi

129
1
4

score 7 · Answer 4 · edited Aug 23 '13 at 07:00

7

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)

edited Aug 23 '13 at 07:00

nKandel

2,533
1
27
47

answered Jul 16 '13 at 13:07

Chandramani

821
1
11
11

this query is slow to process. – user3312649 Nov 29 '17 at 07:39

score 1 · Answer 5 · answered Nov 12 '17 at 16:39

1

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.

answered Nov 12 '17 at 16:39

Gil Baggio

11,049
3
46
33

update one table with data from another

5 Answers5

Linked

Related