概述
MERGE語句是Oracle9i新增的文法,用來合并UPDATE和INSERT語句。通過MERGE語句,根據一張表或子查詢的連接配接條件對另外一張表進行查詢,連接配接條件比對上的進行UPDATE,無法比對的執行INSERT。這個文法僅需要一次全表掃描就完成了全部工作,執行效率要高于INSERT+UPDATE
而PostgreSQL中不直接支援這個文法,但PostgreSQL可以使用WITH Queries (Common Table Expressions)的方法實作相同的功能。
下面我們就來一起看一下
語句詳解
下面這條SQL是把test2表中的資料merge到test1表中,其中主鍵字段為id
WITH upsert AS (
UPDATE test1
SET col1 = test2.col1
FROM test2
WHERE test1.id = test2.id
RETURNING test1.*
)
INSERT INTO test01
SELECT *
FROM test2
WHERE NOT EXISTS (
SELECT 1
FROM upsert b
WHERE test2.id = b.id
);
其實這段SQL的的重點就是利用了postgresql的一個update特性————RETURNING,傳回一個update的結果集,因為查詢條件的存在(也因為它是主鍵,是唯一),就會将兩張表重疊的部分給過濾出來,再用where not exists将這些重疊的部分給忽略掉。這樣就将資料merge進去了
小測試
建兩張表
postgres=# create table test1(id int primary key,name text);
CREATE TABLE
postgres=#
postgres=# create table test2(id int primary key,name text);
CREATE TABLE
資料部分重疊
postgres=# select * from test1;
id | name
----+-------
1 | aaaaa
2 | aaaaa
3 | aaaaa
4 | aaaaa
5 | aaaaa
(5 rows)
postgres=# select * from test2;
id | name
----+-------
4 | aaaaa
5 | aaaaa
6 | bbbbb
7 | bbbbb
8 | bbbbb
9 | bbbbb
(6 rows)
執行merge語句
用test2 這張表去更新test1 ,會将test1 中沒有的資料插入,有的則不會改變
postgres=# WITH upsert AS (
UPDATE test1
SET name = test2.name
FROM test2
WHERE test1.id = test2.id
RETURNING test1.*
)
INSERT INTO test1
SELECT *
FROM test2
WHERE NOT EXISTS (
SELECT 1
FROM upsert b
WHERE test2.id = b.id
);
INSERT 0 4
postgres=# select * from test1;
id | name
----+-------
1 | aaaaa
2 | aaaaa
3 | aaaaa
4 | aaaaa
5 | aaaaa
6 | bbbbb
7 | bbbbb
8 | bbbbb
9 | bbbbb
(9 rows)
可以看到,資料已經更新進來了
一個注意點
在我實際的業務場景更新中,我發現一個需要注意的地方,那就是客戶的test2表,ID列不是主鍵,且有許多重複
檢視ID列有多少重複的SQL如下,如果為0,則說明沒有重複值
select count(*) from users_purse where id in (select id from users_purse group by id having COUNT(*)>1)
如果遇到這種情況,有可能就會出錯,因為test1.ID是不可重複的,是以就可能需要先對其做去重處理distinct。