在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屋！