Is it possible to negate a where clause?
e.g.
DELETE * FROM table WHERE id != 2;
This question is related to
mysql
sql
where-clause
negate
Best solution is to use
DELETE FROM table WHERE id NOT IN ( 2 )
WHERE id <> 2
should work fine...Is that what you are after?
delete from table where id <> 2
edit: to correct syntax for 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)
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
Yes. If memory serves me, that should work. Our you could use:
DELETE FROM table WHERE id <> 2
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.
You could do the following:
DELETE * FROM table WHERE NOT(id = 2);
Use <>
to negate the where clause.
Source: Stackoverflow.com