Updating all datetime column values across a complete MySQL database

Question

some help/advice please if i may.

I have a complete MySQL database on my laptop that i use for all manner of testing that i want to update. Its a DB from 6 years ago and i dont have to ability to get another copy, its just too big now. The one i use is 60GB+ and thats without the last 7 years data. 200+GB now i beleive.

There are 1,704 various date time columns across 799 tables that i want to simply add +6 years to every datetime value. The reason you maybe wondering is that there are traffic light systems in the DB and app that are red, amber and green flags worldwide etc. The data is so old now everything is technically red flagged against timelines and schedules etc.

So let the games begin.

This returns all columns that i need to update if it is NOT NULL;

select
    table_name, column_name, data_type
FROM 
    information_schema.columns
where 
    table_schema NOT IN ('sys','information_schema','sakila','world') AND
    data_type = 'datetime'

Columns

And run the following update statement on the values in the column collection;

update THE_TABLE
set THE_COLUMN = date_add(THE_COLUMN, INTERVAL 6 year)
where THE_COLUMN IS NOT NULL

EDIT: I am struggling with the code that can accomplish this. How to cross over from the columns list to the underlying data looping through the update statement

question from:https://stackoverflow.com/questions/65938566/updating-all-datetime-column-values-across-a-complete-mysql-database

Answer 1

Waitting for answers