ansaurus

Question

Answer 1

+1 A:

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

UPDATE table SET column = NULL;

Gumbo 2010-10-06 08:11:33

Gumbo, I have tried that but all I get is an empty string.

jim 2010-10-06 08:12:48

Now, let me clarify. Through php I am using PDO and updating through a query.

jim 2010-10-06 08:13:28

The MySQL manual states that if the column does not allow NULL values, setting it to NULL will result in the default value for the data type (e.g. an empty string). Are you 100% that your column allows NULL? Can you show us the CREATE TABLE?

Zor 2010-10-06 08:15:41

I am 1000% sure. I've checked it twice. Let me look one more time.

jim 2010-10-06 08:16:30

Yes, it is in fact null. I can see the NULL produced by my frnt end software in the data that should be null.

jim 2010-10-06 08:17:32

Answer 2

+2 A:

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)

Daniel Vassallo 2010-10-06 08:11:40

Thanks Daniel. I'm kind of convinced that this issue may lie in my application code

jim 2010-10-06 08:19:05

Using the string value `"NULL"` does give you the same output. :)

Gumbo 2010-10-06 08:31:07

@Gumbo: You're right :) ... I somehow believed it would be wrapped in quotes if it was a string value... Anyway, I updated the query to return only rows where the column `IS NULL`.

Daniel Vassallo 2010-10-06 08:41:32

Answer 3

A:

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

steven spielberg 2010-10-06 08:27:35

ansaurus

tags:

views:

answers:

How to update column with null value

related questions