Set value to NULL in MySQL

Question

I want a value to be set to NULL if nothing is put into the text box in the form I'm submitting. How can I make this happen? I've tried inserting 'NULL' but this just adds the word NULL into the field.

I'm not sure what code I should provide for this, I'm just writing an UPDATE query.

You'll have to insert the word `null` *without* the quotes.. — Mike Christensen, Feb 16 '12 at 15:50

score 330 · Accepted Answer · edited May 13 '16 at 04:49

330

Don't put NULL inside quotes in your update statement. This should work:

UPDATE table SET field = NULL WHERE something = something

edited May 13 '16 at 04:49

Darryl Hein

142,451
95
218
261

answered Feb 16 '12 at 15:49

Fosco

38,138
7
87
101

score 22 · Answer 2 · answered Feb 16 '12 at 15:49

22

You're probably quoting 'NULL'. NULL is a reserved word in MySQL, and can be inserted/updated without quotes:

INSERT INTO user (name, something_optional) VALUES ("Joe", NULL);
UPDATE user SET something_optional = NULL;

answered Feb 16 '12 at 15:49

Andrew Ensley

11,611
16
61
73

score 11 · Answer 3 · answered Feb 16 '12 at 15:49

11

UPDATE MyTable
SET MyField = NULL
WHERE MyField = ''

answered Feb 16 '12 at 15:49

Bassam Mehanni

14,796
2
33
41

I think that the problem is that he's using: 'NULL' instead of NULL. – Nir Alfasi Feb 17 '12 at 02:33
But, with some care, this could fix existing wanna-be-null empty fields :P – Erenor Paz Dec 04 '15 at 08:57

score 7 · Answer 4 · answered Feb 16 '12 at 15:49

7

You should insert null, not the string of 'NULL'.

answered Feb 16 '12 at 15:49

xdazz

158,678
38
247
274

score 6 · Answer 5 · answered Feb 16 '12 at 15:49

6

Use NULL (without the quotes around it).

UPDATE users SET password = NULL where ID = 4

answered Feb 16 '12 at 15:49

gen_Eric

223,194
41
299
337

score 6 · Answer 6 · answered Feb 16 '12 at 15:49

if (($_POST['nullfield'] == 'NULL') || ($_POST['nullfield'] == '')) {
   $val = 'NULL';
} else {
   $val = "'" . mysql_real_escape_string($_POST['nullfield']) . "'";
}

$sql = "INSERT INTO .... VALUES ($val)";

if you put 'NULL' into your query, then you're just inserting a 4-character string. Without the quotes, NULL is the actual null value.

score 3 · Answer 7 · answered Feb 16 '12 at 15:51

3

Assuming the column allows a null setting,

$mycolupdate = null; // no quotes

should do the trick

answered Feb 16 '12 at 15:51

Mr Griever

4,014
3
23
41

1

... assuming that you don't cast `$mycolupdate` to string at a later stage. – Álvaro González Oct 07 '13 at 16:27

score 2 · Answer 8 · answered May 02 '15 at 10:37

The answers given here are good but i was still struggling to post NULL and not zero in mysql table.

Finally i noted the problem was in the insert query that i was using

   $quantity= "NULL";
   $itemname = "TEST";

So far so good.

My insert query was bad.

   mysql_query("INSERT INTO products(quantity,itemname) 
   VALUES ('$quantity','$itemname')");

I corrected query to read.

   mysql_query("INSERT INTO products(quantity,itemname) 
   VALUES ('".$quantity."','$itemname')");

So the $quantity is outside of the main string. My sql table now accepted to record null quantity instead of 0

I would also recommend abandoning the deprecated mysql_ functions and switch to mysqli or, my preference, PDO. — muttley91, May 02 '15 at 22:30

score 2 · Answer 9 · answered Feb 03 '17 at 06:19

The problem you had is most likely because mysql differentiates between null written in capital letters and null written in small case.

So if you used an update statement with null, it would not work. If you set it to NULL, it would work fine.

Thanks.

score -1 · Answer 10 · answered Apr 06 '23 at 20:28

-1

right click on it and choose "clear field content"

answered Apr 06 '23 at 20:28

Ramzi Haddad

9
1

Set value to NULL in MySQL

10 Answers10

Linked

Related