CODEWITHSUNDEEP
mysql
Robert
Robert

Reputation: 10380

MySql updating column that is null

I was trying to update a column value that has a null value.

Table:

+------------+-----------+-------+
| product_no | name      | price |
+------------+-----------+-------+
|          1 | Cheese    |  9.99 |
|       NULL | Meat      | 17.00 |
|          2 | Pepperoni |  NULL |
+------------+-----------+-------+

Update:

UPDATE products SET product_no = 6 WHERE product_no = NULL;

Output:

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Table definition:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| product_no | int(11)       | YES  |     | NULL    |       |
| name       | varchar(255)  | YES  |     | NULL    |       |
| price      | decimal(10,2) | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

Why is this not updating to 6?

Upvotes: 0

Views: 38

Answers (3)

Robert
Robert

Reputation: 10380

The reason is because NULL is special.

MySql states:

To test for NULL, use the IS NULL and IS NOT NULL operators...

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL.

Hence:

UPDATE products SET product_no = 6 WHERE product_no IS NULL;

Upvotes: 0

Puhua
Puhua

Reputation: 17

NULL value is special, use:

WHERE product_no IS NULL

or

WHERE product_no <=> NULL

Upvotes: 0

zedfoxus
zedfoxus

Reputation: 37029

Try this:

UPDATE products SET product_no = 6 WHERE product_no is NULL;

Upvotes: 1

Related Questions