1

Im trying to delete all rows that have a value in the column ' toevoeging '

I made this query: 

DELETE FROM gebruiksoppervlakte WHERE toevoeging = NOT NULL;

Why is this not possible? So I want to keep the rows that have NULL Thanks!

warnerst
  • 181
  • 3
  • 5
  • 12
  • 7
    `IS NOT NULL`, rather than `= NOT NULL`. – Michael Berkowski Mar 29 '13 at 13:39
  • See also [Not equal <> != operator in T-SQL on NULL](http://stackoverflow.com/questions/5658457/not-equal-operator-in-t-sql-on-null) – LittleBobbyTables - Au Revoir Mar 29 '13 at 13:43
  • `NULL` is not a value that can be directly compared similarly to other values. It has its own logic. For example, `NULL` itself does **not** equal `NULL`. See http://www.sqlservercentral.com/blogs/steve_jones/2010/10/13/common-sql-server-mistakes-_1320_-equals-null/ – PM 77-1 Mar 29 '13 at 13:48

3 Answers3

6

You need to use IS NOT NULL instead of toevoeging = NOT NULL:

DELETE 
FROM gebruiksoppervlakte 
WHERE toevoeging IS NOT NULL;

NULL is something that lacks a value. So using IS NULL or IS NOT NULL means that your column is either lacking a value or not lacking a value.

Edit, this will delete everything in your table that has a value in the toevoeging column. If you have empty strings that you want to delete, then you will want to use:

DELETE 
FROM gebruiksoppervlakte 
WHERE toevoeging = '';

See Demo.

If you want to delete the rows with null values then you will use:

DELETE 
FROM gebruiksoppervlakte 
WHERE toevoeging IS NULL;

See Demo

Edit #2, based on our conversation in chat you are trying to delete all rows where there is a value but that value is not an empty string. Then the remaining rows need to be updated to null in the column. As a result you need to use both a delete and an update:

DELETE 
FROM gebruiksoppervlakte 
WHERE toevoeging IS NOT NULL
  and toevoeging <> '';


update gebruiksoppervlakte
set toevoeging = null;

See Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • @warnerst This will delete all rows where toevoeging is not null so if you have a value in each row, then it will delete everything. What are you trying to delete? Are the empty cells, null or empty strings? – Taryn Mar 29 '13 at 13:59
  • Oh they where empty, but not ' NULL ' . So I think it should work now – warnerst Mar 29 '13 at 14:01
  • @warnerst If you want to delete the rows with empty strings, see my edit. – Taryn Mar 29 '13 at 14:03
  • @warnerst Are you trying to delete the `null` rows? – Taryn Mar 29 '13 at 14:05
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/27186/discussion-between-bluefeet-and-warnerst) – Taryn Mar 29 '13 at 14:08
0

When using the WHERE clause to locate rows that have a NULL value, never use the “= NULL” comparison, instead always use the IS NULL or IS NOT NULL comparison operators.

So the correct query is :

DELETE FROM gebruiksoppervlakte WHERE toevoeging IS NOT NULL;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Blood-HaZaRd
  • 2,049
  • 2
  • 20
  • 43
0

Because NULLs cannot be compared, the following works exactly the same as ... WHERE toevoeging IS NOT NULL :

DELETE FROM gebruiksoppervlakte
WHERE toevoeging = toevoeging
   ;
wildplasser
  • 43,142
  • 8
  • 66
  • 109