How to update column with null value

Question

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?

Answer 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)

Answer 2

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

UPDATE table SET column = NULL;

Answer 3

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

UPDATE studentdetails
SET contactnumber = 9098979690
WHERE contactnumber IS NULL;

Answer 4

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.

Another way to put 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

Answer 5

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)

Answer 6

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.

Answer 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

Answer 8

use is instead of =

Eg: Select * from table_name where column is null

Answer 9

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!

Answer 10

if you set NULL for all records try this:

UPDATE `table_name` SET `column_you_want_set_null`= NULL

OR just set NULL for special records use WHERE

UPDATE `table_name` SET `column_you_want_set_null`= NULL WHERE `column_name` = 'column_value' 

Answer 11

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

Answer 12

On insert we can use

$arrEntity=$entity->toArray();        
    foreach ($arrEntity as $key => $value) {    
        if (trim($entity->$key) == '' && !is_null($entity->$key) && !is_bool($entity->$key)){
        unset($entity->$key);
        }
    }

On update we can use

$fields=array();
foreach ($fields as $key => $value) {
        if (trim($value) == '' && !is_null($value) && !is_bool($value)){
            $fields[$key] = null;
        }
    }

Answer 13

To set as NULL on a column you need to have the column to allow NULL first, otherwise you'll definitely get error.

To adit the column just run this syntax

ALTER TABLE [table_name] CHANGE [column_name] [column_name] VARCHAR(10) NULL DEFAULT NULL

Answer 14

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;