CODEWITHSUNDEEP
mysqlsql
user1373317
user1373317

Reputation: 116

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

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.

Upvotes: 4

Views: 4133

Answers (2)

user756519
user756519

Reputation:

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

Upvotes: 5

aldux
aldux

Reputation: 2804

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

Upvotes: 2

Related Questions