DELETE FROM `Person` WHERE `t15`=NULL;

[gert cuykens]

Tell me i am dreaming...mysql> SELECT * FROM `Person`;
+------+
| t15 |
+------+
| NULL |
| NULL |
| jljk |
| jljk |
| jljk |
| jljk |
+------+
6 rows in set (0.00 sec)

mysql> DELETE FROM `Person` WHERE `t15`=NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM `Person` WHERE `t15`='NULL';
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM `Person` WHERE `t15`='';
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM `Person` WHERE `t15`=(backslash)N;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM `Person` WHERE `t15`='(backslash)N';
Query OK, 0 rows affected (0.00 sec)

mysql>Why doesnt it delete the first two records ?

[TheBearMay]

You can't equal null as null has an undefined value. Try:

DELETE FROM `Person` WHERE `t15` is Null;

[gert cuykens]

Fieeew.... worrying it wasnt posible at all. I was making documention like "if you want to delete 1 empty record go back to column menu, drop the columns and its gone :D" Now all i need is a "if" and a variable called "$grrrr_delete_it_d****" Still think ='' should had been working too :D

[gert cuykens]

:confused: sometimes it works sometimes it doesnt ? Is it possible there are two kinds of null ? one '' and a one NULL ?
mysql> select t22 from Person;
+------+
| t22 |
+------+
| tt |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)

mysql> DELETE FROM `Person` WHERE t22 is Null;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM `Person` WHERE `t22` is Null;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM `Person` WHERE `t22`='NULL ';
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM `Person` WHERE `t22`='NULL';
Query OK, 0 rows affected (0.00 sec)

mysql>

[gert cuykens]

ok thats it i suwing mysql for making me go crazy :D

mysql> select t22 from Person;
+------+
| t22 |
+------+
| NULL |
| tt |
| ff |
| ff |
+------+
4 rows in set (0.00 sec)

mysql> DELETE FROM `Person` WHERE `t22`='';
Query OK, 1 row affected (0.00 sec)

mysql>

[mark_yieh]

NULL and " " are two completely different things. NULL means "unknown" or no value, much like N/A. " " means empty string, which means that it has a value and the value is nothing. The NULL value is never true in comparison to any other value even NULL. So if you tested NULL = NULL it will still return false. Therefore you cannot use exp = NULL syntax to test if the exp is in fact NULL. The proper way of doing this is to use the IS NULL or IS NOT NULL or IFNULL functions. So for example:

SELECT * FROM table_name WHERE field_name IS NULL; // correct

SELECT * FROM table_name WHERE field_name = NULL; // incorrect


If you used the first example shown above, then any field that was assigned " " should not be returned. So for example if you created a table like this:

CREATE TABLE contacts (name VARCHAR(50), occupation VARCHAR(50), residence VARCHAR(50));

and populated like this:

INSERT INTO contacts VALUES
("Tony", NULL, NULL),
("Hillary", "", "" );

And you used this query statement:

SELECT * FROM contacts WHERE occupation IS NULL;

then this will only return the first item, whereas if you used a query statement like this:

SELECT * FROM contacts WHERE occupation = "";

then is will only return the second item in the table.

HOpe this clears up the confusion between NULL and " " (empty strings);