在Postgres中的同一CLAUSE替代中合并具有两个条件的语句 [英] Merge statement with two conditions in same CLAUSE alternative in Postgres
问题描述
在使用WITH子句将Oracle Merge迁移到Postgres时,我遇到了问题.
I am facing issue while migration of Oracle Merge into Postgres using WITH Clause.
下面是ORACLE的示例代码:
Below is the example code from ORACLE:
SELECT * FROM source;
ID STATUS DESCRIPTION
---------- ---------- -----------------------
1 20 Description of level 1
2 10 Description of level 2
3 20 Description of level 3
4 10 Description of level 4
5 20 Description of level 5
目标表如下:
SELECT * FROM destination;
1 20 Description of level 1
2 10 Description of level 2
3 20 Description of level 3
4 10 Description of level 4
5 20 Description of level 5
6 10 Description of level 6
7 20 Description of level 7
8 10 Description of level 8
9 20 Description of level 9
10 10 Description of level 10
我在ORACLE中具有Merge实现,如下所示:合并时:
I have Merge implementation in ORACLE like below: When merged :
MERGE INTO destination d
USING source s
ON (s.id = d.id)
WHEN MATCHED THEN
UPDATE SET d.description = 'Updated'
DELETE WHERE d.status = 10;
5 rows merged.
SELECT * FROM destination;
ID STATUS DESCRIPTION
---------- ---------- -----------------------
1 20 Updated
3 20 Updated
5 20 Updated
6 10 Description of level 6
7 20 Description of level 7
8 10 Description of level 8
9 20 Description of level 9
10 10 Description of level 10
8 rows selected.
我能够转换在MATCHED& amp;使用CTE的UNMATCHED子句.但是,如果我在同一子句中有两个条件,则不确定该怎么做(DELETE和UPDATE).
I was able to convert a Merge which has one condition in both MATCHED & UNMATCHED clauses using CTE. But not sure how to do (both DELETE & UPDATE), if I have two conditions in the same clause.
我了解答案a_horse_with_no_name已共享.但是对于以下情况我是否需要嵌套CTE感到困惑.
Edited: I understood the answer a_horse_with_no_name has shared. But got confused whether I need to do a nested CTEs in the below case.
MERGE INTO destination d
USING source s
ON (s.id = d.id)
WHEN MATCHED THEN
UPDATE SET d.description = 'Updated'
DELETE WHERE d.status = 10
WHEN NOT MATCHED THEN
INSERT (ID, STATUS, DESCRIPTION) VALUES (s.id,s.status,s.description);
如果我的merge语句也未匹配,那么我该怎么做.
If my merge statement has NOT MATCHED too then how do I do it.
推荐答案
我不确定这是否是最有效的方法,但是它可以满足您的要求:
I am not sure if this is the most efficient way to do it, but it does what you want:
with updated as (
update destination d
set description = 'Updated'
from source s
where s.id = d.id
and d.status <> 10
returning d.id
)
delete from destination
where id not in (select id from updated)
and exists (select *
from source s
where s.id = destination.id);
它首先更新目标
中存在于 source
中且状态为10以外的行.然后删除所有未更新且存在于中的行.源
表.
It first updates rows in destination
that exist in source
and have a status other than 10. Then it deletes those that were not updated and exist in the source
table.
在线示例: http://rextester.com/KTTOX89581
这篇关于在Postgres中的同一CLAUSE替代中合并具有两个条件的语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!