概述

MERGE语句是Oracle9i新增的语法,用来合并UPDATE和INSERT语句。通过MERGE语句,根据一张表或子查询的连接条件对另外一张表进行查询,连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE

而PostgreSQL中不直接支持这个语法,但PostgreSQL可以使用WITH Queries (Common Table Expressions)的方法实现相同的功能。

下面我们就来一起看一下

语句详解

下面这条SQL是把test2表中的数据merge到test1表中,其中主键字段为id

  1. WITH upsert AS (
  2. UPDATE test1
  3. SET col1 = test2.col1
  4. FROM test2
  5. WHERE test1.id = test2.id
  6. RETURNING test1.*
  7. )
  8. INSERT INTO test01
  9. SELECT *
  10. FROM test2
  11. WHERE NOT EXISTS (
  12. SELECT 1
  13. FROM upsert b
  14. WHERE test2.id = b.id
  15. );

其实这段SQL的的重点就是利用了postgresql的一个update特性————RETURNING,返回一个update的结果集,因为查询条件的存在(也因为它是主键,是唯一),就会将两张表重叠的部分给过滤出来,再用where not exists将这些重叠的部分给忽略掉。这样就将数据merge进去了

小测试

建两张表

  1. postgres=# create table test1(id int primary key,name text);
  2. CREATE TABLE
  3. postgres=#
  4. postgres=# create table test2(id int primary key,name text);
  5. CREATE TABLE

数据部分重叠

  1. postgres=# select * from test1;
  2. id | name
  3. ----+-------
  4. 1 | aaaaa
  5. 2 | aaaaa
  6. 3 | aaaaa
  7. 4 | aaaaa
  8. 5 | aaaaa
  9. (5 rows)
  10. postgres=# select * from test2;
  11. id | name
  12. ----+-------
  13. 4 | aaaaa
  14. 5 | aaaaa
  15. 6 | bbbbb
  16. 7 | bbbbb
  17. 8 | bbbbb
  18. 9 | bbbbb
  19. (6 rows)

执行merge语句

用test2 这张表去更新test1 ,会将test1 中没有的数据插入,有的则不会改变

  1. postgres=# WITH upsert AS (
  2. UPDATE test1
  3. SET name = test2.name
  4. FROM test2
  5. WHERE test1.id = test2.id
  6. RETURNING test1.*
  7. )
  8. INSERT INTO test1
  9. SELECT *
  10. FROM test2
  11. WHERE NOT EXISTS (
  12. SELECT 1
  13. FROM upsert b
  14. WHERE test2.id = b.id
  15. );
  16. INSERT 0 4
  17. postgres=# select * from test1;
  18. id | name
  19. ----+-------
  20. 1 | aaaaa
  21. 2 | aaaaa
  22. 3 | aaaaa
  23. 4 | aaaaa
  24. 5 | aaaaa
  25. 6 | bbbbb
  26. 7 | bbbbb
  27. 8 | bbbbb
  28. 9 | bbbbb
  29. (9 rows)

可以看到,数据已经更新进来了

一个注意点

在我实际的业务场景更新中,我发现一个需要注意的地方,那就是客户的test2表,ID列不是主键,且有许多重复
查看ID列有多少重复的SQL如下,如果为0,则说明没有重复值

  1. select count(*) from users_purse where id in (select id from users_purse group by id having COUNT(*)>1

如果遇到这种情况,有可能就会出错,因为test1.ID是不可重复的,所以就可能需要先对其做去重处理distinct。

0 评论  
添加一条新评论