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.
I will add this as an answer as it might help others and it was suggested on the comments section.
Whenever you are running DML (insert, update, deletes) scripts and the data is acting up or you are receiving errors that doesn't look related to the script you are running, always check whether you have triggers associated to the table.
This was the case of OP here.