MySql Update a table with data from another table

Question

I have two tables that are identical in structure. Table1 holds moderated data, table2 holds the rest.

Table 1

+------+--------+---------------+--------+-----------+
| "id" | "name" | "description" | "type" | "country" |
+------+--------+---------------+--------+-----------+
| "1"  | "a"    | "x"           | "1"    | "US"      |
| "2"  | "b"    | "x"           | "1"    | "UK"      |
+------+--------+---------------+--------+-----------+

Table 2

+------+-----------+-----------------+--------+-----------+----------+
| "id" |  "name"   |  "description"  | "type" | "country" | "status" |
+------+-----------+-----------------+--------+-----------+----------+
| "1"  | "Title 1" | "Description 1" | "1"    | "US"      | "0"      |
| "2"  | "Title 2" | "Description 2" | "10"   | "UK"      | "0"      |
+------+-----------+-----------------+--------+-----------+----------+

I run the below sql in order to update table 1 with data from table 2, and it works well. The only problem is, I need to specify the id in both places. If I were to specify it only in one place, where would it go?

UPDATE table1 dest, 
       (SELECT name, 
               description 
        FROM   table2 
        WHERE  id = 1) src 
SET    dest.name = src.name, 
       dest.description = src.description 
WHERE  dest.id = 1;

The way this thing goes is:

UPDATE table1 SET name AND description =
(
   SELECT name, description from table2
   WHERE id=1 AND country=us and type=10
) WHERE id=idfromselect AND country=countryfromselect AND type=typefromselect

I can't figure out where to put the id and remaining conditions. Can you help?

You can use a join condition see this link it might help stackoverflow.com/questions/224732/… — learner
– learner, Commented Jun 4, 2013 at 11:40

Kickstart · Accepted Answer · 2013-06-04 11:35:40Z

1

Do it as a join, put the id in the join condition and just check the id in the WHERE clause.

Something like this:-

UPDATE table1 dest INNER JOIN table2 src ON dest.id = src=id
SET dest.name = src.name, dest.description = src.description 
WHERE dest.id=1 ;

Any other restrictions can just be added to the WHERE clause

answered Jun 4, 2013 at 11:35

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fabio · Accepted Answer · 2013-06-04 11:41:54Z

1

I think you can use an INNER JOIN query to update your table1 basing on data from table2 and put your condition in WHERE clauses

UPDATE table1 a
INNER JOIN table2 b
ON a.id = b.id
SET a.name = b.name,
a.description = b.description
WHERE a.id=1;

edited Jun 4, 2013 at 11:41

answered Jun 4, 2013 at 11:36

Fabio

23.5k12 gold badges57 silver badges66 bronze badges

Collectives™ on Stack Overflow

MySql Update a table with data from another table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related