在 Postgres 中的相同 CLAUSE 替代項中合併具有兩個條件的語句

我能夠使用 CTE 轉換在 MATCHED 和 UNMATCHED 子句中都有一個條件的 Merge。但如果我在同一個子句中有兩個條件，不知道該怎麼做（都是DELETE& ）。UPDATE下面是範例：

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 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.

如果上面的程式碼將如下所示：

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);

請幫助我如何在 Postgres 中使用 WITH 重新編寫上述程式碼。

據我所知，Postgres (10) 不允許MERGE語句。你可以用這種方式模擬它：

create table new_data (id int, status int, description text);
insert into new_data values
(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');

create table destination (id int, status int, description text);
insert into destination values
( 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');

delete from destination d
using new_data n
where n.id = d.id
and   n.status = 10;

update destination d
set    description = 'Updated'
from   new_data n
where  d.id = n.id;

insert    into destination
select    n.id, n.status, n.description
from      new_data n
left join destination d
on        n.id = d.id
where     d.id is null
and       n.status <> 10;

2 行受影響

3 行受影響

✓

select *
from   destination
order by id;

編號 | 狀態 | 描述 
-: | -----: | :----------------------
 1 | 20 | 更新 
 3 | 20 | 更新 
 5 | 20 | 更新 
 6 | 10 | 6級說明
 7 | 20 | 7級說明
 8 | 10 | 8級說明
 9 | 20 | 9級說明
10 | 10 | 10級說明

dbfiddle在這裡

使用 INSERT - ON CONFLICT DO

begin;

    insert into destination
    select n.id, n.status, n.description
    from   new_data n
    on conflict (id) 
    do update
    set description = 'Updated';

    delete from destination
    where  id in (select id from new_data where status = 10);

commit;

dbfiddle在這裡

使用 CTE

WITH 
  u AS
( update destination d
  set    description = 'Updated'
  from   new_data n
  where  d.id = n.id
    and  n.status is distinct from 10
),
  d AS
( delete from destination d
  using new_data n
  where n.id = d.id
    and n.status = 10
),
  i AS
( insert    into destination
  select    n.id, n.status, n.description
  from      new_data n
  left join destination d
  on        n.id = d.id
  where     d.id is null
)
select 1 ;

dbfiddle在這裡

引用自：https://dba.stackexchange.com/questions/197278