Merge statement with two conditions in same CLAUSE alternative in Postgres I was able to convert a Merge which has one condition in both MATCHED &amp; UNMATCHED clauses using CTE. But not sure how to do (both DELETE &amp; UPDATE), if I have two conditions in the same clause.
Below is the sample example:

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


I have Merge implementation in ORACLE like below:

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.


If the above code will have as below:

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


Please help me how to re write the above code using WITH in Postgres.
As far as I know, Postgres (10) doesn't allow MERGE statement. You can simulate it in this way:




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 &lt;&gt; 10;



2 rows affected

3 rows affected

✓






select *
from   destination
order by id;



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



dbfiddle here

Using 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 here

Using 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 here