How to update column with null value

问题:

I am using mysql and need to update a column with a null value. I have tried this many different ways and the best I have gotten is an empty string.

Is there a special syntax to do this?

回答1:

No special syntax:

CREATE TABLE your_table (some_id int, your_column varchar(100));

INSERT INTO your_table VALUES (1, 'Hello');

UPDATE your_table
SET    your_column = NULL
WHERE  some_id = 1;

SELECT * FROM your_table WHERE your_column IS NULL;
+---------+-------------+
| some_id | your_column |
+---------+-------------+
|       1 | NULL        |
+---------+-------------+
1 row in set (0.00 sec)

回答2:

NULL is a special value in SQL. So to null a property, do this:

UPDATE table SET column = NULL;

回答3:

Use IS instead of = This will solve your problem example syntax:

UPDATE studentdetails
SET contactnumber = 9098979690
WHERE contactnumber IS NULL;

回答4:

Remember to look if your column can be null. You can do that using

mysql> desc my_table;

If your column cannot be null, when you set the value to null it will be the cast value to it.

Here a example

mysql> create table example ( age int not null, name varchar(100) not null );
mysql> insert into example values ( null, "without num" ), ( 2 , null );
mysql> select * from example;
+-----+-------------+
| age | name        |
+-----+-------------+
|   0 | without num |
|   2 |             |
+-----+-------------+
2 rows in set (0.00 sec)

mysql> select * from example where age is null or name is null;
Empty set (0.00 sec)

回答5:

For those facing a similar issue, I found that when 'simulating' a SET = NULL query, PHPMyAdmin would throw an error. It's a red herring.. just run the query and all will be well.

回答6:

Another possible reason for the empty string, rather than a true null is that the field is an index or is part of an index. This happened to me: using phpMyAdmin, I edited the structure of a field in one of my tables to allow NULLs by checking the "Null" checkbox then hitting the "Save" button. "Table pricing has been altered successfully" was displayed so I assumed that the change happened -- it didn't. After doing an UPDATE to set all of those fields to NULL, they were, instead, set to empty strings, so I took a look at the table structure again and saw that the "Null" column for that field was set to 'no'. That's when I realized that the field was part of the Primary key!

回答7:

If you want to set null value using update query set column value to NULL (without quotes) update tablename set columnname = NULL

However, if you are directly editing field value inside mysql workbench then use (Esc + del) keystroke to insert null value into selected column

回答8:

if you follow

UPDATE table SET name = NULL

then name is "" not NULL IN MYSQL means your query

SELECT * FROM table WHERE name = NULL not work or disappoint yourself

回答9:

I suspect the problem here is that quotes were entered as literals in your string value. You can set these columns to null using:

UPDATE table SET col=NULL WHERE length(col)<3;

You should of course first check that these values are indeed "" with something like:

SELECT DISTINCT(col) FROM table WHERE length(col)<3;

回答10:

use is instead of =

Eg: Select * from table_name where column is null

回答11:

In the above answers, many ways and repetitions have been suggested for the same. I kept looking for an answer as mentioned is the question but couldn't find here.

But opposite of the above question "update a column with a null value" Could be "UPDATE ALL THE ROWS IN THE COLUMN TO NULL"

In such a situation following works

update table_name
set field_name = NULL
where field_name is not NULL;

is as well is not works in mysql