delete the records in sql based on satisfaction of 2 condition-CodePudding

I want to delete the records in the table based on 2 condition.

This is my query :

delete from customer where productid not in (1,2,3,4,5) and manufacturerid not in ( 8,10)

The problem is I am able to see customer with productid 6,7,8 etc where manufacturerid is 8 and 10.

I want to delete all the product except id 1,2,3,4,5 which has manufacturerid 8 and 10 alone but my query is not working as expected.

CodePudding user response：

According to your explanation you want to delete as follows with OR and not AND. This means that will will delete where either, or both, of the conditions is true.

DELETE FROM customer 
WHERE
  productid NOT IN (1,2,3,4,5) 
OR
  manufacturerid NOT IN ( 8,10) ;

CodePudding user response：

I think what you want is actually a NOT with an AND:

DELETE C
FROM Customer C
WHERE NOT (C.productid IN (1,2,3,4,5) AND C.manufacturerid IN (8,10));