admin 管理员组

文章数量: 887021

upsert

#简介
完整博客请查看我的个人博客:SimpleBlog
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表,并插入如下数据。

id(主键)name
1tom
2john

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

id(主键)name
1tom
2mike

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

id(主键)name
1tom
2mike
3anna

##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)BEGINUPDATE table_A SET Value = @Value WHERE Id = @Id;END
ELSEBEGININSERT INTO table_A (Id, Value) VALUES(@Id, @Value)END

还可以使用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 --源数据表数据的不存在被操作表中,执行以下语句
语句;

例子:

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表数据为准,执行后表数据如下:

id(主键)name
1aaa
4ddd

本文标签: upsert