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 differ开发者_开发百科ent ways and the best I have gotten is an empty string.

Is there a special syntax to do this?

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)

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

UPDATE table SET column = NULL;

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

UPDATE studentdetails
SET contactnumber = 9098979690
WHERE contactnumber IS NULL;

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

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)

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.

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

use is instead of =

Eg: Select * from table_name where column is null

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!

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' 

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

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;
        }
    }

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;