delete functionality incremental load in sql server

Question

I need to delete records that are present in the destination table but not in the source table. The primary key in the destination table is an auto_increment ID which is not there in the source table. Both the source and destination tables contain a set of unique key combinations which can be used to uniquely identify the rows in either tables. What is the approach that I should follow? How can I delete if I am to use multiple column combinations as the unique key and not one primary key(not there in source)?

 delete from dest_table
 where (uniq_key_col1,uniq_key_col2) not in (
   select dest.uniq_key_col1,dest.uniq_key_col2 
   from dest_table dest
   join source_table source
   on dest.uniq_key_col1=source.uniq_key_col1
   and dest.uniq_key_col2=source.uniq_key_col2
 )

This is how it should ideally look (provided just for clarity and please ignore the error in where clause because of multiple columns)

Possible duplicate of [How to Delete Records NOT IN](https://stackoverflow.com/questions/14953624/how-to-delete-records-not-in) — SqlKindaGuy, Jul 04 '18 at 10:36

score 2 · Answer 1 · answered Jul 04 '18 at 10:33

2

You can use exists. ie:

delete from dest_table
 where not exists (
   select * 
   from source_table source
   where dest_table.uniq_key_col1=source.uniq_key_col1
   and dest_table.uniq_key_col2=source.uniq_key_col2
 );

answered Jul 04 '18 at 10:33

Cetin Basoz

22,495
3
31
39

This appears to be the first answer using `not exists`, so it deservers to be upvoted. – Gordon Linoff Jul 04 '18 at 11:33
@GordonLinoff I agree - Even though i was only 5 seconds behind :P – SqlKindaGuy Jul 04 '18 at 11:34

score 2 · Answer 2 · edited Jul 04 '18 at 11:31

2

You can do like this :

DELETE
FROM dbo.dest a 
WHERE NOT EXISTS (
      SELECT 1
        FROM dbo.source1 b
       WHERE a.id1 = b.ID1 and a.id2 = b.id2
      )

edited Jul 04 '18 at 11:31

Gordon Linoff

1,242,037
58
646
786

answered Jul 04 '18 at 10:34

SqlKindaGuy

3,501
2
12
29

score 1 · Answer 3 · answered Jul 04 '18 at 10:36

Another option for you

DELETE dest_table
FROM dest_table
    LEFT JOIN source_table
        ON dest_table.uniq_key_col1 = source_table.uniq_key_col1
            AND dest_table.uniq_key_col2 = source_table.uniq_key_col2
WHERE source_table.uniq_key_col1 IS NULL

katsho · Accepted Answer · 2018-07-04T11:34:53.060

1

It sounds like NOT EXISTS is what you need

DELETE d FROM dest_table d 
WHERE NOT EXISTS (SELECT (PUT_APPROPRIATE_COLUMNS_HERE) from source_table s 
   WHERE d.col1 = s.col
   AND d.col2 = s.col2
   ... etc for other columns
   )

Note the table aliasing, you need that. And it might be more appropriate to use an inner join, if that is possible with your data.

edited Jul 04 '18 at 11:34

answered Jul 04 '18 at 10:38

katsho

303
1
4

as in, list the columns you want in the SELECT, or * if preferred – katsho Jul 04 '18 at 12:08
What would it matter if you have a list of columns or * or any literal like 1 or 'x' in a select that is used for EXISTS query? – Cetin Basoz Jul 04 '18 at 12:11
could do - the asker seemed to want a list of columns so I indicated where to list them if wanted. '1' is also an 'appropriate' column – katsho Jul 04 '18 at 12:19
EXISTS query doesn't deal with the list of columns and no matter what returns a simple boolean value. – Cetin Basoz Jul 04 '18 at 12:20

delete functionality incremental load in sql server

4 Answers4