CODEWITHSUNDEEP
mysql
carljon21
carljon21

Reputation: 15

UPDATE table WHERE other column is NULL

I'd like to update my table in that point to set an Destroy timestamp if there is none. The Destroy timestamp is defined as 5-10 days more than Created.

decayworld:

+------------+---------+
| Created    | Destroy |
+------------+---------+
| 1515765721 |    NULL |
| 1515765743 |    NULL |
| 1515765743 |    NULL |
| 1515765743 |    NULL |
| 1515765744 |    NULL |
+------------+---------+

What's wrong with my code?

UPDATE decayworld AS a
SET a.Destroy=(SELECT FLOOR (a.Created+RAND()*(864000-432000+1))+432000)
WHERE a.Destroy=(NULL)
;

Actually I got 0 rows are affected.

Upvotes: 1

Views: 43

Answers (3)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28698

You need to check for is null.

When the Destroy column is null, your condition becomes null = null which returns null - which is not true!

Try the below code:

select null = null; 
select null is null;

Upvotes: 0

Ananta
Ananta

Reputation: 700

You cannot do field = NULL, instead use field IS NULL

Try bellow code: 

UPDATE decayworld AS a
SET a.Destroy=(SELECT FLOOR (a.Created+RAND()*(864000-432000+1))+432000)
WHERE a.Destroy IS NULL;

If you want more info on using NULL values in mysql : https://dev.mysql.com/doc/refman/5.7/en/working-with-null.html

Upvotes: 1

marchaos
marchaos

Reputation: 3444

In MySQL (an others), you need to use IS NULL rather than = NULL which will equate to false.

Upvotes: 0

Related Questions