SQL WHERE condition is not equal to?

Question

Is it possible to negate a where clause?

e.g.

DELETE * FROM table WHERE id != 2;

Solution 1

You can do like this

DELETE FROM table WHERE id NOT IN ( 2 )

OR

DELETE FROM table WHERE id <>  2 

As @Frank Schmitt noted, you might want to be careful about the NULL values too. If you want to delete everything which is not 2(including the NULLs) then add OR id IS NULL to the WHERE clause.

Solution 2

Your question was already answered by the other posters, I'd just like to point out that

 delete from table where id <> 2

(or variants thereof, not id = 2 etc) will not delete rows where id is NULL.

If you also want to delete rows with id = NULL:

delete from table where id <> 2 or id is NULL

Solution 3

You could do the following:

DELETE * FROM table WHERE NOT(id = 2);

Solution 4

delete from table where id <> 2

edit: to correct syntax for MySQL

Solution 5

Use <> to negate the where clause.

View more solutions

Comments

[Joshua Pinter]

FYI, id != NULL does not work as you'd expect.

[Frank Vilea]

I tried this command a couple of times.. but it didn't seem to work until I realized that DELETE in MySQL does not use the * ..

[Praveen Lobo]

There you go, edited the answer to remove the * Thanks for pointing out.

[Byson]

Just a general comment for people who got here wondering why their != NULL isn't working: "You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL." (from the MySQL documentation). So that means you have to use IS NOT NULL.

[Kareem]

Works well if the value is not numeric WHERE NOT(id = 'two');

[RAMM-HDR]

when using NOT IN () , i get ( 'SQL logic error near "WHERE": syntax error' )