MySql updating column that is null

Question

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?

Nothing is equal, nor not equal, to NULL - not even NULL! A value either IS or IS NOT NULL. — Strawberry
– Strawberry, Commented Jul 14, 2015 at 0:43

zedfoxus · Accepted Answer · 2015-07-14 00:44:00Z

1

Try this:

UPDATE products SET product_no = 6 WHERE product_no is NULL;

answered Jul 14, 2015 at 0:44

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Puhua · Accepted Answer · 2015-07-14 00:50:35Z

0

NULL value is special, use:

WHERE product_no IS NULL

or

WHERE product_no <=> NULL

answered Jul 14, 2015 at 0:50

Puhua

175 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

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;

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 14, 2015 at 0:50

Robert

10.4k20 gold badges90 silver badges143 bronze badges

Collectives™ on Stack Overflow

MySql updating column that is null

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related