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?
This question is related to
mysql
sql-update
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;
}
}
Use IS
instead of =
This will solve your problem
example syntax:
UPDATE studentdetails
SET contactnumber = 9098979690
WHERE contactnumber IS NULL;
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
NULL
is a special value in SQL. So to null a property, do this:
UPDATE table SET column = NULL;
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!
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;
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
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'
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
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.
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)
Source: Stackoverflow.com