How to insert and delete from and to the same table in the same query?

Is there any way to select something from table A then delete & insert those records from/to table B (kind of updating B with values from A)?

Let’s say I have two following tables:

------- Table A -------
id   name   value   id2
1    'a'    25      6
1    'a'    33      8
2    'c'    63      4

------- Table B -------
id   name   value   id3
1    'a'    12      6
2    'c'    63      7
5    'd'    18      9

I came up with this:

WITH CTE AS (
  SELECT *, xRank 
  FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY id2 DESC) AS xRank 
    FROM A
  ) AS I 
  WHERE xRank = 1 AND <some other conditions>
), DLT AS (
  DELETE FROM B USING CTE T WHERE id=T.id AND name=T.name
)
INSERT INTO B (id, name, value) 
SELECT id, name, value 
FROM CTE T 
WHERE <some condition>;

This is working expect when it fails for “duplicate key value” error. However I thought maybe the delete is ran prior to insert but apparently they are executed is the same transaction.

At the end, Table B should become:

------- Table B -------
id   name   value   id3
1    'a'    33      NULL
2    'c'    63      NULL
5    'd'    18      9

Anyone has any simple way to do that (without using temp tables and by doing only once the SELECT part of course)?

Answer

Provided that you have a unique constraint for (id, name) in B, I think that you need INSERT...ON CONFLICT:

WITH CTE AS (
  SELECT *, xRank 
  FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY id2 DESC) AS xRank 
    FROM A
  ) AS I 
  WHERE xRank = 1
)
INSERT INTO B (id, name, value) 
SELECT id, name, value 
FROM CTE 
ON CONFLICT(id, name) DO 
UPDATE 
SET value = EXCLUDED.value,
    id3 = NULL;

See the demo.
Results:

| id  | name | value | id3 |
| --- | ---- | ----- | --- |
| 1   | a    | 33    |     |
| 2   | c    | 63    |     |
| 5   | d    | 18    | 9   |