SQL WHERE condition not equal?

Question

Is it possible to cancel the where clause?

eg.

DELETE * FROM table WHERE id != 2; 

Answer 1

You can do it as follows:

 DELETE FROM table WHERE id NOT IN ( 2 ) 

OR

 DELETE FROM table WHERE id <> 2 

As @Frank Schmitt pointed out, you can be careful with NULL values. If you want to delete everything that is not 2 (including NULL), add OR id IS NULL to the WHERE clause.

Answer 2

Other posters already answered your question, I would like to note that

  delete from table where id <> 2 

(or its variants, 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 

Answer 3

 delete from table where id <> 2 

edit: to fix MySQL syntax

Answer 4

Use <> to cancel the where clause.

Answer 5

You can do the following:

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

Answer 6

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

Answer 7

Look at formal logic and algebra. Type expression

 A & B & (D | E) 

can be canceled in several ways:

• The obvious way:

   !( A & B & ( D | E ) ) 

• The above can also be counted, 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 NOT (!) Throughout the expression to which it is applied, inverting operators and eliminating double negatives as you move:

    !A | !B | ( !D & !E ) 

So, in the general case, any where clause can be canceled in accordance with the above rules. Denial of this

 select * from foo where test-1 and test-2 and ( test-3 OR test-4 ) 

is an

 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 ) 

What's better? This is a very context sensitive question. Only you can solve it.

Remember, however, that using NOT may affect what the optimizer may or may not do. You may receive a less optimal query plan.

Answer 8

Yes. If memory helps me, this should work. We could use:

 DELETE FROM table WHERE id <> 2 

Answer 9

I just solved this problem. If you use <> or are not in a variable, i.e. null, this will result in an error. Therefore, instead of <> 1, you should check it as follows:

  AND (isdelete is NULL or isdelete = 0)