upsert----非标准DML语句

简介

DML：全称Data manipulation Language，是数据库操作语句，例如select、insert、update等等。 按照国际化标准组织1992年发布的数据库SQL语言标准 （ ISO/IEC 9075:1992, Database Language SQL- July 30, 1992），是不包括upsert，所以仅有部分数据库支持upsert操作。

详解

upsert 是 update 和 insert 的组合。如果表中已经存在指定值，则更新现有行，如果指定值不存在，则插入新行。

语法

UPSERT INTO table_name (字段1, 字段2,...) VALUES (字段1值, 字段2值, ...);

实例

创建people表，并插入如下数据。

UPSERT INTO people (id, name) VALUES (2, 'mike'); 更新id为2的行数据，表数据如下：

UPSERT INTO people (id, name) VALUES (3, 'anna'); 插入id为3的行数据，表数据如下：

MySQL实现upsert

INSERT INTO INTO table_name (字段1,字段2,...) VALUES (字段1值, 字段2值, ...) ON DUPLICATE KEY UPDATE; 例子： INSERT INTO INTO people (id,name) VALUES (2,'reva') ON DUPLICATE KEY UPDATE;

postgre实现upsert

INSERT INTO INTO table_name (字段1,字段2,...) VALUES (字段1值, 字段2值, ...) ON CONFLICT conflict_target conflict_action; conflict_target ：指定判断字段 conflict_action ：指定操作 例子： INSERT INTO people (id,name) VALUES (2,'reva') ON CONFLICT (id) DO UPDATE;

SQL server实现upsert

可以先查询表中是否有数据，然后再做插入或更新操作。

IF EXISTS (SELECT 1 FROM table_A WHERE Id = @Id)
    BEGIN
        UPDATE table_A SET Value = @Value WHERE Id = @Id;
    END
ELSE
    BEGIN
        INSERT INTO table_A (Id, Value) VALUES(@Id, @Value)
    END

SQL

还可以使用MERGE实现 语法：

MERGE target_table  --被操作表
USING source_table  --源数据表，可以是子查询
ON target_table.column=source_table.column --两个表的某个列作匹配
WHEN MATCHED THEN --被操作表数据的存在源数据表中时，执行以下语句
语句
WHEN NOT MATCHED THEN --被操作表数据的不存在源数据表中，执行以下语句
语句
WHEN NOT MATCHED BY SOURCE THEN --源数据表数据的不存在被操作表中，执行以下语句
语句;

SQL

例子：

MERGE people as target
USING (values('1','aaa'),('4','ddd')) as source(id,name)
ON target.id=source.id
WHEN MATCHED THEN 
update set target.name=source.name
WHEN NOT MATCHED THEN 
insert values(source.id,source.name)
WHEN NOT MATCHED BY SOURCE THEN 
delete;

people表存在源表数据则更新，不存在插入，people表有源没有则删除，也可以不执行删除操作（把delete删除）。以最初people表数据为准，执行后表数据如下：