CODEWITHSUNDEEP
mysql
DevOps
DevOps

Reputation: 438

Query not working with adding where clause after case statement on multiple columns

I have following query which is not working with WHERE clause.Any help will be appreciated.

UPDATE abc SET
col1 = CASE
WHEN a1_date < now() THEN "xyz"
END,
col2 = CASE
WHEN a2_date < now() THEN "xyz"
END,
col3 = CASE
WHEN a3_date < now() THEN "xyz"
END

WHERE a1_date != NULL AND a2_date != NULL

Upvotes: 2

Views: 90

Answers (2)

Patrick W
Patrick W

Reputation: 1567

To quote the MySQL Documentation

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

Therefore your comparison check right now will not work as intended.

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

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

Change your WHERE clause to use IS NOT NULL:

WHERE a1_date IS NOT NULL AND a2_date IS NOT NULL

Upvotes: 2

Related Questions