My SQL 5.7 Add a year interval to a date field

How do I update 4 fields in my sql by one year

EventStartDate      2017-01-08 00:00:00  to  2018-01-08 00:00:00  for example

I need to do a bulk update to a load of records on my wordpress website and increment the dates by 1 year for an existing category. is there a quick way of doing this once I've sorted out the joins?

Full Texts      meta_id      post_id      meta_key      meta_value
      Edit      Delete      31415      4265      _tribe_modified_fields      a:34:{s:8:"post_tag";i:1498896141;s:12:"_EventOrig...
      Edit      Delete      31416      4265      _EventOrigin      event-aggregator
      Edit      Delete      31417      4265      _EventShowMapLink       
      Edit      Delete      31418      4265      _EventShowMap       
      Edit      Delete      31419      4265      _EventAllDay      yes
      Edit      Delete      31420      4265      _EventStartDate      2017-01-08 00:00:00
      Edit      Delete      31421      4265      _EventEndDate      2017-01-08 23:59:59
      Edit      Delete      31422      4265      _EventStartDateUTC      2017-01-08 00:00:00
      Edit      Delete      31423      4265      _EventEndDateUTC      2017-01-08 23:59:59
      
      update `wp_t0jnpbhfm3_postmeta`
      set meta_value = DATE_ADD('_EventStartDate', INTERVAL 1 YEAR)
      where meta_value ='_EventStartDate' and post_id=4265
      
      
      
      SELECT *
FROM  `wp_t0jnpbhfm3_postmeta`
WHERE post_id =4265



select 'meta_value' = DATE_ADD('meta_value', INTERVAL 1 YEAR)
FROM  `wp_t0jnpbhfm3_postmeta`
where meta_key ='_EventStartDate' and post_id=4265
Chris MichalczukConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dustin SaundersDirector of OperationsCommented:
Looks like you have mixed up values in the query.

update `wp_t0jnpbhfm3_postmeta`
set meta_value = DATE_ADD(meta_value, INTERVAL 1 YEAR)
where meta_key='_EventStartDate' and post_id=4265

Open in new window


Be sure to back up your database before you run queries against it if you're not super familiar with SQL!
NerdsOfTechTechnology ScientistCommented:
1. Backup Database

2. Run these four queries:

 UPDATE `wp_t0jnpbhfm3_postmeta`
 SET meta_value = DATE_ADD(meta_value, INTERVAL 1 YEAR)
 WHERE meta_key='_EventStartDate' and post_id=4265;

 UPDATE `wp_t0jnpbhfm3_postmeta`
 SET meta_value = DATE_ADD(meta_value, INTERVAL 1 YEAR)
 WHERE meta_key='_EventEndDate' and post_id=4265;

 UPDATE `wp_t0jnpbhfm3_postmeta`
 SET meta_value = DATE_ADD(meta_value, INTERVAL 1 YEAR)
 WHERE meta_key='_EventStartDateUTC' and post_id=4265;

 UPDATE `wp_t0jnpbhfm3_postmeta`
 SET meta_value = DATE_ADD(meta_value, INTERVAL 1 YEAR)
 WHERE meta_key='_EventEndDateUTC' and post_id=4265;

Open in new window


If you wanted to update by category(ies) you would have to JOIN the category table (ADD category(ies) to the WHERE clause) appropriately and remove post_id from your queries WHERE clause above.

OPTIONALLY:
If there are only a few post_id's you are dealing with you could replace post_id=4265 with:
post_id IN (4265, ....)

Open in new window

and list the ID's in the list separated by commas for all four queries.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NerdsOfTechTechnology ScientistCommented:
I think both answers apply; backup before the update queries are run; run the full series of update queries to solve.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.