Search code examples
mysqldatabasesequelpro

MySQL: DELETE FROM using a multi conditional "not equal to" WHERE

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.

Solution

  • 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.