SQL WHERE condition is not equal to?

mysql sql where-clause negate
Frank Vilea picture Frank Vilea · May 27, 2011 · Viewed 254.4k times · Source

Is it possible to negate a where clause?

e.g.

DELETE * FROM table WHERE id != 2;

Answer

Praveen Lobo picture Praveen Lobo · May 27, 2011

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.

Related questions

SELECTING with multiple WHERE conditions on same column

Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column... My table is a very simple linking setup for applying …

Read More
Detect if value is number in MySQL

Is there a way to detect if a value is a number in a MySQL query? Such as SELECT * FROM myTable WHERE isANumber(col1) = true

Read More
WHERE vs HAVING

Why do you need to place columns you create yourself (for example select 1 as "number") after HAVING and not WHERE in MySQL? And are there any downsides instead of doing WHERE 1 (writing the whole definition instead of a column name)?

Read More