mysql中ON DUPLICATE KEY UPDATE用法及各数据库对应语法

先声明一点，ON DUPLICATE KEY UPDATE为Mysql特有语法，语句的作用为，当insert已经存在的记录时，执行Update

MYSQL:
student表结构如下：

表中的主键为id，现要插入一条数据，id为1，name为第一次插入的名字，正常写法为：

INSERT INTO testtest.studentstudent (recordidrecordid, namename) VALUES ('1', '张三');

执行后刷新表数据，结果如下：

再次执行以上语句，报错主键"1"已存在：

我们修改为以下语句执行：

insert into student(RECORDID,name) VALUES (1,'李四')
on duplicate key update name=values(name);

可以看到将原来recordid=1的name值修改为了“李四”，而不是执行插入语句，解决了重复插入问题。

当执行多条插入语句时，可以用以下写法：

insert into student(RECORDID,name) VALUES (1,'张三'),(2,'李四'),(3,'王五')
on duplicate key update name=values(name);

执行之后，表数据为

ORACLE:
在oralce中比较繁琐，用merge into语法可以实现此功能，语句如下：

MERGE INTO STUDENT a1 Using (SELECT 1 RECORDID,'张三' name from dual) a2

on(a1.RECORDID=a2.RECORDID)

WHEN MATCHED THEN

UPDATE SET a1.name='张三' WHERE a1.RECORDID=1

WHEN NOT MATCHED THEN

INSERT (RECORDID,name) VALUES (1,'张三');



MERGE INTO STUDENT a1 Using (SELECT 2 RECORDID,'李四' name from dual) a2

on(a1.RECORDID=a2.RECORDID)

WHEN MATCHED THEN

UPDATE SET a1.name='李四' WHERE a1.RECORDID=2

WHEN NOT MATCHED THEN

INSERT (RECORDID,name) VALUES (2,'李四');



MERGE INTO STUDENT a1 Using (SELECT 3 RECORDID,'王五' name from dual) a2

on(a1.RECORDID=a2.RECORDID)

WHEN MATCHED THEN

UPDATE SET a1.name='王五' WHERE a1.RECORDID=3

WHEN NOT MATCHED THEN

INSERT (RECORDID,name) VALUES (3,'王五');

金仓：

INSERT INTO student(RECORDID, NAME) VALUES ('1', '张三') ON CONFLICT(RECORDID) DO UPDATE SET RECORDID = EXCLUDED.RECORDID,NAME=EXCLUDED.NAME;
INSERT INTO student(RECORDID, NAME) VALUES ('2', '李四') ON CONFLICT(RECORDID) DO UPDATE SET RECORDID = EXCLUDED.RECORDID,NAME=EXCLUDED.NAME;
INSERT INTO student(RECORDID, NAME) VALUES ('3', '王五') ON CONFLICT(RECORDID) DO UPDATE SET RECORDID = EXCLUDED.RECORDID,NAME=EXCLUDED.NAME;

报错信息的原因是ON CONFLICT(RECORDID) ，中的RECORDID不为主键或者，为复合主键，比如(RECORDID,NAME)为主键

达梦：
与oracle相同。

sql server：

IF NOT EXISTS (
SELECT
1
FROM
student
WHERE
recordid = '1'
) INSERT INTO student (recordid,name)
VALUES
(1, '张三')
ELSE
UPDATE student
SET recordid = '1',
name = '张三'
WHERE
recordid = '1';


IF NOT EXISTS (
SELECT
1
FROM
student
WHERE
recordid = '2'
) INSERT INTO student (recordid,name)
VALUES
('2', '李四')
ELSE
UPDATE student
SET recordid = '2',
name = '李四'
WHERE
recordid = '2';

IF NOT EXISTS (
SELECT
1
FROM
student
WHERE
recordid = '3'
) INSERT INTO student (recordid,name)
VALUES
('3', '王五')
ELSE
UPDATE student
SET recordid = '3',
name = '王五'
WHERE
recordid = '3';