SQL WHERE condition is not equal to?
MysqlSqlWhere ClauseNegateMysql Problem Overview
Is it possible to negate a where clause?
e.g.
DELETE * FROM table WHERE id != 2;
Mysql Solutions
Solution 1 - Mysql
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 - Mysql
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 - Mysql
You could do the following:
DELETE * FROM table WHERE NOT(id = 2);
Solution 4 - Mysql
delete from table where id <> 2
edit: to correct syntax for MySQL
Solution 5 - Mysql
Use <>
to negate the where clause.
Solution 6 - Mysql
Look back to formal logic and algebra. An expression like
A & B & (D | E)
may be negated in a couple of ways:
-
The obvious way:
!( A & B & ( D | E ) )
-
The above can also be restated, you just need to remember some properties of logical expressions:
-
!( A & B )
is the equivalent of(!A | !B)
. -
!( A | B )
is the equivalent of(!A & !B)
. -
!( !A )
is the equivalent of (A).Distribute the NOT (!) across the entire expression to which it applies, inverting operators and eliminating double negatives as you go along:
!A | !B | ( !D & !E )
-
So, in general, any where clause may be negated according to the above rules. The negation of this
select *
from foo
where test-1
and test-2
and ( test-3
OR test-4
)
is
select *
from foo
where NOT( test-1
and test-2
and ( test-3
OR test-4
)
)
or
select *
from foo
where not test-1
OR not test-2
OR ( not test-3
and not test-4
)
Which is better? That's a very context-sensitive question. Only you can decide that.
Be aware, though, that the use of NOT can affect what the optimizer can or can't do. You might get a less than optimal query plan.
Solution 7 - Mysql
WHERE id <> 2
should work fine...Is that what you are after?
Solution 8 - Mysql
Best solution is to use
DELETE FROM table WHERE id NOT IN ( 2 )
Solution 9 - Mysql
Yes. If memory serves me, that should work. Our you could use:
DELETE FROM table WHERE id <> 2
Solution 10 - Mysql
I was just solving this problem. If you use <> or is not in on a variable, that is null, it will result in false. So instead of <> 1, you must check it like this:
AND (isdelete is NULL or isdelete = 0)