DebRaj DebRaj - 1 year ago 48
SQL Question

Update a range of post dates with SQL query in phpMyadmin

I am trying to update a number of post(around 7000) in a WordPress database post dates within certain range of dates using this SQL query:

UPDATE `wp_posts`
SET post_date="2015-07-22 20:31:30"
WHERE post_date BETWEEN '2015-07-31 00:00:00' AND '2015-12-14 00:00:00'
AND post_status="publish"

I need to find posts which has dates from 2015-07-31 00:00:00 to 2015-12-14 00:00:00 and update them with 2015-07-22 20:31:30 and make them published

My server running with SQL server 5.5.44

What I am doing wrong here?


Answer Source

In your query you're selecting the posts with the publish status instead of update them to publish. Try this query instead:

UPDATE `wp_posts`
SET post_date="2015-07-22 20:31:30", post_status="publish"
WHERE post_date BETWEEN '2015-07-31 00:00:00' AND '2015-12-14 00:00:00'
AND post_type = "post"

I added a post_type condition as you probably don't want to change revisions or any other posts type.