How do I copy data from one table to another with WHERE clause?

mysql, sql

Solution

You need an UPDATE statement with the JOIN clause and not the INSERT statement because you already have the data in both the tables and you would like to copy values in one of the columns from one table to the other.

Click here to view the demo in SQL Fiddle.

Script:

CREATE TABLE table1
(   id          INT NOT NULL 
    , value     INT NOT NULL
    , number    INT NOT NULL
);

CREATE TABLE table2
(       id      INT         NOT NULL
    ,   name    VARCHAR(30) NOT NULL
    ,   value   INT         NOT NULL
    ,   rid     INT         NOT NULL
);

INSERT INTO table1 (id, value, number) VALUES
  (1,  0, 111),
  (2,  0, 222),
  (3,  0, 333),
  (4, 10, 444);

INSERT INTO table2 (id, name, value, rid) VALUES
  (1, 'abc', 123, 1),
  (2, 'def', 345, 2),
  (3, 'efg', 456, 3),
  (4, 'ghi', 567, 4);

UPDATE      table1
INNER JOIN  table2
ON          table1.id       = table2.rid
SET         table1.value    = table2.value;

Data before running the UPDATE:

table1:
ID  VALUE NUMBER
--  ----- ------
1     0    111
2     0    222
3     0    333
4    10    444

table2:
ID  NAME  VALUE RID
--  ----  ----- ---
1   abc   123   1
2   def   345   2
3   efg   456   3
4   ghi   567   4

Data after running the UPDATE:

table1:
ID  VALUE NUMBER
--  ----- ------
1    123    111
2    345    222
3    456    333
4    567    444

table2:
ID  NAME  VALUE RID
--  ----  ----- ---
1   abc   123   1
2   def   345   2
3   efg   456   3
4   ghi   567   4

Problem

I have two tables that look like this `TABLE1`: `id` `value` `number` `TABLE2`: `id` `name` `value` `rid` I want to copy the `value` from TABLE2 into TABLE1 where TABLE2.rid=TABLE1.id. I have tried the following: ``` INSERT INTO TABLE1 (value) SELECT value FROM TABLE2 WHERE TABLE2.rid=TABLE1.id. ``` I can't do this however because I haven't joined TABLE1 and TABLE2 -- if I try to do that I get a syntax error. Any help would be appreciated.

Original source