SQL DELETE query with NOT IN clause not working

Question

i have a simple delete query i have two tables table1 and table2

the query is

DELETE FROM table1
WHERE  end_time NOT IN (SELECT end_time
                        FROM   table2
                        WHERE  comp_name = 'component')
       AND id = 'id'

the query does not delete the record what am i missing here i tried to change the first condition with second but it deletes without checking the condition 2

what are the alternatives here?

i am wondering what is the actual problem here the query runs without errors but it is not correct.

Try to run each of the condition in a select query to see if getting any results — roeygol, Jan 02 '15 at 20:09
there is no result in the select query when i use the both conditions but there is common results on each conditions — user3786134, Jan 02 '15 at 20:18
Try `exists` instead of `in`. `In` can act 'strange' when you got null values in it. — bobthedeveloper, Jan 02 '15 at 20:27
The select recommendation is a great way to determine what rows will be deleted without actually deleting them. Use it to tune your query without having to re-insert data each time. Since you are checking if end_time from table1 is in table2, remember the "IN" operator expects a precise match. Even if sub-seconds are different, there will not be a match. — Grax32, Jan 02 '15 at 20:30
@user3786134 - Good question. Here are some related posts - https://stackoverflow.com/questions/14479005/not-exists-vs-not-in and https://stackoverflow.com/questions/173041/not-in-vs-not-exists — Erran Morad, Jan 02 '15 at 20:31

score 4 · Accepted Answer · edited May 23 '17 at 10:26

May be alias should fix your problem since the both the tables have same column name end_time. Outer query column can be referred inside the subquery that might be the problem.

DELETE FROM table1 
WHERE  end_time NOT IN (SELECT b.end_time
                        FROM   table2 B
                        WHERE  b.comp_name = 'component')
       AND id = 'id'

Update : Try this delete because you may have null values in your sub query where NOT IN will fail. Check this post link

DELETE a
FROM   table1 a
WHERE  NOT EXISTS (SELECT 1
                   FROM   table2 B
                   WHERE  b.comp_name = 'component'
                          AND a.end_time = b.end_time)
       AND id = 'id'

SQL DELETE query with NOT IN clause not working

1 Answers1