Reputation: 1933

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?

Upvotes: 193

Answers (14)

Nizam Khir

Reputation: 1

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

Upvotes: 0

Nitish Kumar Pal

Reputation: 2986

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

Upvotes: 13

Balasaheb Bhise

Reputation: 245

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

Upvotes: 0

mamal

Reputation: 2006

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'

Upvotes: 1

korubilli krishna chaitanya

Reputation: 567

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

UPDATE studentdetails
SET contactnumber = 9098979690
WHERE contactnumber IS NULL;

Upvotes: 29

mayank nigam

Reputation: 51

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

Upvotes: 2

Daniel Dunn

Reputation: 77

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.

Upvotes: 4

Thiago Mata

Reputation: 2959

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)

Upvotes: 8

ReverseEMF

Reputation: 536

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!

Upvotes: 1

Cyber Gangster

Reputation: 11

use is instead of =

Eg: Select * from table_name where column is null

Upvotes: 1

Dylan

Reputation: 1

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;

Upvotes: -1

Daniel Vassallo

Reputation: 344551

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)

Upvotes: 289

Steven Spielberg

Reputation:

if you follow