0

If I have two tables with identical column definitions, I could do the following:

DELETE FROM table_2 WHERE pkey NOT IN (select pkey from table_1);

There are similar statements to insert into B anything not already in it but in A, and to update any rows with matching pks in A.

Can all of these statements be be performed at once with MERGE INTO?

MERGE INTO table_1 b
     USING table_2 a
        ON b.pkey = a.pkey
      WHEN MATCHED 
           THEN UPDATE SET col1 = a.col1, col2 = a.col2 ...
      WHEN NOT MATCHED 
           THEN INSERT (col1, col2) values (a.col1, a.col2)

Is there any way of determining that the destination has a row not matched in the source, such that I could then do the DELETE? If there's a clever way to manipulate the ON clause, I can't see it or think of how to search for it. The documentation doesn't make this very clear.

John O
  • 4,863
  • 8
  • 45
  • 78
  • According to [the manual](https://www.postgresql.org/docs/current/sql-merge.html) only the `WHEN MATCHED` path has a DELETE option. –  Mar 15 '23 at 19:28
  • @a_horse_with_no_name I know that it can delete rows, that's a given. The trouble is that the on clause doesn't seem to perform like a left join, and so it can never "match" a row that's in the destination, but not present in the source. – John O Mar 15 '23 at 19:35
  • Yes. WHEN MATCHED refers to a match found between the source and the destination table. What you are looking for would be a third "WHEN" option that simply isn't implemented. So I think it's pretty clear in the manual (that you can't do what you want with the MERGE statement) –  Mar 15 '23 at 19:42
  • A manual explicitly states what is possible. Anything that is not stated in the manual is not possible. or would you expect the manual to contain a list of everything that a statement does **not** do? Hardly possible. "This statement won't alter the table structure. This statement won't drop a corresponding trigger. This statement won't change the definition of views using the tables...." –  Mar 15 '23 at 19:48
  • 1
    If you have properly defined foreign keys then the situation you are trying to correct will not/cannot exist. But as indicated the `merge` statement cannot fix it; you will need to identify anf remove the invalid rows (your above delete would work) then build the appropriate FKs. – Belayer Mar 15 '23 at 22:20

1 Answers1

0

No. This is not possible.

Only rows that have a match in both tables can be deleted