108

Is it possible to negate a where clause?

e.g.

DELETE * FROM table WHERE id != 2;
tru.d
  • 555
  • 2
  • 6
  • 23
Frank Vilea
  • 8,323
  • 20
  • 65
  • 86

10 Answers10

178

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.

Praveen Lobo
  • 6,956
  • 2
  • 28
  • 40
  • 1
    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 * .. – Frank Vilea May 27 '11 at 19:59
  • There you go, edited the answer to remove the * Thanks for pointing out. – Praveen Lobo May 27 '11 at 21:45
  • 6
    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](http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html)). So that means you **have to use** `IS NOT NULL`. – Byson Aug 20 '14 at 14:31
  • when using NOT IN () , i get ( 'SQL logic error near "WHERE": syntax error' ) – The Doctor Jun 19 '22 at 16:11
32

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
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
14

You could do the following:

DELETE * FROM table WHERE NOT(id = 2);
BuZZ-dEE
  • 6,075
  • 12
  • 66
  • 96
Magicianeer
  • 2,190
  • 1
  • 15
  • 12
13
delete from table where id <> 2



edit: to correct syntax for MySQL

Fosco
  • 38,138
  • 7
  • 87
  • 101
9

Use <> to negate the where clause.

Brandon
  • 68,708
  • 30
  • 194
  • 223
7

WHERE id <> 2 should work fine...Is that what you are after?

JNK
  • 63,321
  • 15
  • 122
  • 138
7

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.

Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135
6

Best solution is to use 

DELETE FROM table WHERE id NOT IN ( 2 )
tru.d
  • 555
  • 2
  • 6
  • 23
5

Yes. If memory serves me, that should work. Our you could use:

DELETE FROM table WHERE id <> 2
dkruythoff
  • 361
  • 1
  • 3
0

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)
Kryptobarons
  • 36
  • 3