Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Copy data from one tabe to another (update existed records)

Copy data from one tabe to another (update existed records)

Author: rootsql Date: 1-11-2011, 22:24

Example shows how to update records in one table with data from another table.

CREATE TABLE table_a (
  id INT(11) DEFAULT NULL,
  column_a INT(11) DEFAULT NULL
);
CREATE TABLE table_b (
  id INT(11) DEFAULT NULL,
  column_b INT(11) DEFAULT NULL
);
INSERT INTO table_a VALUES 
  (1, 10),
  (2, 20),
  (3, 30),
  (4, NULL),
  (5, 0);
INSERT INTO table_b VALUES 
  (1, 1),
  (2, NULL),
  (3, 0),
  (4, 4),
  (5, 5);

Update data:

UPDATE table_a a
  JOIN table_b b
    ON a.id = b.id
SET
  b.column_b = a.column_a;

Result table:

SELECT * FROM table_b;
+------+----------+
| id   | column_b |
+------+----------+
|    1 |       10 |
|    2 |       20 |
|    3 |       30 |
|    4 |     NULL |
|    5 |        0 |
+------+----------+

Tags: Tips

Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
Information
Members of Guests cannot leave comments.