INSERT or UPDATE from another table in mysql

I have two tables, with same schema –

create table test1 (
a INT NOT NULL ,
b INT NOT NULL ,
c INT,
PRIMARY KEY (a,b)
);

create table test2 (
a INT NOT NULL ,
b INT NOT NULL ,
c INT,
PRIMARY KEY (a,b)
);

I want to insert values from test2 table into test1, but if the row with same primary key already exist, update it. I know in mysql you can do similar thing with ON DUPLICATE KEY UPDATE like –

INSERT INTO test1 VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=3;

But I dont know how to do the above query with a SELECT from another table. What I am looking for is a query of form –

INSERT INTO test2 
SELECT a, b, c FROM test1
ON DUPLICATE KEY UPDATE c = c + t.c 
(Select a, b, c from tests1)t;

This query is obviously invalid. I would appreciate if somebody can make a valid query out of it.

Answer

This should work for you:

INSERT INTO test2 
SELECT a, b, c as c1 FROM test1
ON DUPLICATE KEY UPDATE c = c + c1

Advertisement

Answer