SQL delete statement for a rowset

Question

I need to delete rowset Table 1 (4 row set) by comparing the Table 2 using SQL (status =Loaded only) else keep the row-set for that IG.

Eg, IG 10 data from TABLE 1 will be deleted and the IG 60 data will be remains same as all the status corresponding to that IG not loaded.

Answer 1

You can try the following solution. The inner SELECT gets all IG values which are not successfully loaded. The DELETE itself deletes all rows where the IG value is not in this list (of unsuccessful loaded) IG values.

DELETE FROM Table1 WHERE NOT IG IN (
  SELECT IG FROM Table2 WHERE Status <> 'Loaded'
)

To make sure you delete the right rows you can use the following SELECT first:

SELECT * FROM Table1 WHERE NOT IG IN (
  SELECT IG FROM Table2 WHERE Status <> 'Loaded'
)

demo on dbfiddle.uk