There are a lot of great Stackoverflow questions/answers with regard to this error. However, most (all) are focused on updating the problem field itself. For example, a common solution is to set sql_mode to NO_ENGINE_SUBSTITUTION (thus disabling strict mode) and then updating the field that currently has a 0000-00-00 value.

However, my issue is slightly different. I have a table with multiple date columns (often for the same record) containing malformed data (0000-00-00). If I run a query to update one column, I get this error thrown for one of the other columns — regardless of what sql_mode I set or whether I use UPDATE IGNORE. It’s as if the sql_mode only applies to the column I’m updating, but errors are still thrown for the other columns.

Here’s a stripped down example:
Let’s say I have a table with three columns: id, created_date, modified date, and I have this row:

id | created_date | modified date
---------------------------------
5  | 0000-00-00   | 0000-00-00

This query will error:

UPDATE mytable SET created_date = NULL WHERE id = 5;

and the error will be on the modified_date field.

If I update both fields, I get no error:

UPDATE mytable SET created_date = NULL, modified_date = NULL WHERE id = 5;

Again — I’m setting the sql_mode to ensure I’m not in strict mode or have either NO_ZERO_DATE or NO_ZERO_IN_DATE enabled. Also note that I am running MySQL 8.

For various reasons I can’t easily run updates that affect both columns — I need to do a single column at a time. But can’t seem to get around this error.