Merge statement with two conditions in same CLAUSE alternative 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