How to Update all Dates in a Table

Question

I have a table with 5 million DATETIME records. I want to add a year to all the various datetimes in the table. Can I do it all with a single query? Something like:

SELECT DATE_ADD(*, INTERVAL 1 YEAR);

Or any other way you would recommend. Thanks!

score 9 · Accepted Answer · edited May 23 '17 at 12:24

9

This should do what you want:

UPDATE table SET datefield = DATE_ADD(datefield, INTERVAL 1 YEAR);

If you need to update every table in the database check the answers to this question

edited May 23 '17 at 12:24

Community

1
1

answered Dec 03 '11 at 04:16

Pastor Bones

7,183
3
36
56

2

He wants to do it for *all* datetime fields in the whole database. – Matthew Flaschen Dec 03 '11 at 04:18
1

@MatthewFlaschen - This WILL update all records in a given table. In order to update every table without knowing the table names you'll have to first run the query "SHOW TABLES" then work off the results from that. – Pastor Bones Dec 03 '11 at 04:28
Yes, it will update the "datefield" field in all records. However, his question is much more general, and will probably require use of `INFORMATION_SCHEMA.COLUMNS`. – Matthew Flaschen Dec 03 '11 at 04:31
@MatthewFlaschen: Sorry, I should be clearer. I just need it for one table. Thanks. – Question Overflow Dec 03 '11 at 04:35
Thank a lot Pastor – Polamin Singhasuwich Oct 05 '16 at 05:08

How to Update all Dates in a Table

1 Answers1

Linked