从 mysql 中的另一个表插入或更新 [英] 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)
);

我想将 test2 表中的值插入到 test1 中，但如果具有相同主键的行已经存在，请更新它.我知道在 mysql 中你可以用 ON DUPLICATE KEY UPDATE 做类似的事情 -

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;

但我不知道如何使用另一个表中的 SELECT 执行上述查询.我正在寻找的是表单查询 -

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.

推荐答案

这应该适合你:

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

这篇关于从 mysql 中的另一个表插入或更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！