If the record exists in target but doesn't exist in source, i need to delete it

Question

So, i have two tables, the target table and the source one. I need to delete the rows that exists in the target table, but doesn't exists in the source table.

And the code:

MERGE INTO (SELECT id_car_bk, car_brand_bk, car_type_bk, new_car
              FROM car_catalog_backup) CB
USING (SELECT id_car, car_brand, car_type FROM car_catalog) C
ON (CB.id_car_bk = b.id_car)
WHEN NOT MATCHED THEN
  INSERT
    (CB.id_car_bk, CB.car_brand_bk, CB.car_type_bk)
  VALUES
    (C.id_car, C.car_brand, C.car_type)
WHEN MATCHED THEN
  UPDATE SET CB.car_brand_bk = C.car_brand;

WHEN NOT MATCHED BY SOURCE is the ideal way if you want to manage it within the MERGE — Tony Thomas, Jun 17 '20 at 07:50

Barbaros Özhan · Accepted Answer · 2020-05-01T08:58:55.717

You can use

DELETE car_catalog_backup b 
 WHERE not exists 
        ( SELECT 0
            FROM car_catalog c 
           WHERE b.id_car_bk = c.id_car );

or

DELETE car_catalog_backup b 
 WHERE b.id_car_bk not in 
        ( SELECT c.id_car
            FROM car_catalog c );

assuming car_catalog is the source, and car_catalog_backup is the target. The First one is preferable, since it's more performant.

If your aim is to find out with a MERGE statement similar to your case, then use the following

MERGE INTO car_catalog_backup a
USING (SELECT id_car, car_brand, car_type, car_brand_bk 
         FROM car_catalog
         JOIN car_catalog_backup
           ON id_car_bk = id_car
         ) b
   ON (a.id_car_bk = b.id_car)
 WHEN MATCHED THEN
   UPDATE SET a.new_car = 1
   DELETE
   WHERE a.car_brand_bk != b.car_brand 
 WHEN NOT MATCHED THEN
   INSERT
    (id_car_bk, car_brand_bk, car_type_bk)
   VALUES
    (b.id_car, b.car_brand, b.car_type)

to delete the records matched for id columns ( a.id_car_bk = b.id_car ) but not matched for brand code columns ( a.car_brand_bk != car_brand ) as an example.

Demo

score 0 · Answer 2 · answered Mar 28 '19 at 08:37

0

Delete from target
Where not exists 
( 
    Select 1
    From source 
    Where join of source and target
)

answered Mar 28 '19 at 08:37

Saad Ahmad

393
1
7

score 0 · Answer 3 · answered Mar 28 '19 at 08:55

0

With a left join:

DELETE target 
FROM target LEFT JOIN source 
ON target.someid = source.otherid 
WHERE source.otherid IS NULL;

answered Mar 28 '19 at 08:55

forpas

160,666
10
38
76

doesn t work can u be more specific or try something else? – Andrei GH Mar 28 '19 at 09:06
This is the right syntax for Oracle. Join the 2 tables on a common column and delete the rows that don't match: https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/delete.html – forpas Mar 28 '19 at 09:08
Andrei GH, mr. forpas gave you link to mysql database delete statement. – Amir Kadyrov Mar 28 '19 at 09:22
akk0rd87, i know, but i tried to understand how can i apply it on my code – Andrei GH Mar 28 '19 at 09:26

If the record exists in target but doesn't exist in source, i need to delete it

3 Answers3