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!
3 Answers
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
5 Comments
Taryn
@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?
warnerst
Oh they where empty, but not ' NULL ' . So I think it should work now
Taryn
@warnerst If you want to delete the rows with empty strings, see my edit.
Taryn
@warnerst Are you trying to delete the
null rows?Taryn
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;
Comments
Because NULLs cannot be compared, the following works exactly the same as ... WHERE toevoeging IS NOT NULL :
DELETE FROM gebruiksoppervlakte
WHERE toevoeging = toevoeging
;
IS NOT NULL, rather than= NOT NULL.NULLis not a value that can be directly compared similarly to other values. It has its own logic. For example,NULLitself does not equalNULL. See sqlservercentral.com/blogs/steve_jones/2010/10/13/…1320-equals-null/