2

Hah. So. I've been playing with this particular query where I'm trying to delete a large swath of rows but I end up not doing what I'm expecting. I've run various variations of this query and I'm not having any luck.

Basically I'm trying to do this:

DELETE FROM table WHERE country <>  'MX' OR 'CA';

OR

DELETE FROM table WHERE foobar NOT IN ( 12 OR 5 );

OR 

DELETE FROM table WHERE foobar NOT IN ( 'foo' ) OR ( 'bar' );

And a couple other ideas I had that weren't working. I'm just uploading a fresh dataset for the umpteenth time I'd appreciate some help in the right direction.

romanchukenator
  • 97
  • 1
  • 6
  • 1
    A quick look to the [documentation](http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_in) always helps. – axiac Dec 09 '14 at 17:45
  • Well. Egg on my face. Thank you for this, I've been scouring the DELETE FROM section and couldn't find what I was looking for. – romanchukenator Dec 09 '14 at 17:51

2 Answers2

5

Actually, logical operators like "OR" are applied to conditions but not values.

So, you can use either 

DELETE FROM table WHERE country <> 'MX' AND country <> 'CA';

or 

DELETE FROM table WHERE country NOT IN ('MX', 'CA')

The second one is preferable.

Yeldar Kurmangaliyev
  • 33,467
  • 12
  • 59
  • 101
3

Try this:

 DELETE FROM table WHERE country NOT IN ('MX', 'CA');
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786