Oh no! A series of posts were uploaded to the server in reverse order!

The site uses a view that sorts posts into chronological order (node created time). We can't change the view just to handle these errant posts. We could update all the node created times one at a time in the database, but there's just too many - it'll take all day!

I knew it would be possible to fix this with a little SQL, but I've never had to do something like this before. After some reading and research, this is what I came up with

SET @rownum := 0;UPDATE node SET created = UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL (SELECT @rownum := @rownum + 1) MINUTE)) WHERE nid > 100 AND nid < 200 ORDER BY created DESC;

There's a lot going on in this statement:

SET @rownum := 0; initializes the rownum variable to 0.

You can mostly ignore the date stuff - Drupal uses integer timestamps while MySQL prefers real Dates. My plan is to use the DATE_ADD() function to add ever larger amounts to the starting time (NOW()). This could be a lot simpler just leaving everything as integers, but I like the challenge :)

INTERVAL (SELECT @rownum := @rownum + 1) MINUTE

This is really the key part of the statement. We're doing a subselect right in the middle of the UPDATE statement. This causes rownum to be incremented by 1, and the new value returned, which is then used to update just this row. When the next row is updated, rownum will be incremented again, and so on. So when each node gets its created time updated, the first will be now(), the second now() + 1 minute, the third now() + 2 minutes, etc.

WHERE nid > 100 AND nid < 200

The where clause just specifies which nodes we're updating, nothing special but it is critical. Make sure you're only affecting the rows you want to update.

ORDER BY created DESC

This is the other key part. Ordering for updates works just like it does for select statements. All the rows are getting updated, this lets us specify in what order the updated happen.

So now when we put it all together, the magic happens. The nodes were uploaded in reverse order, remember? That means when we sort by created time in descending order, we get the newest ones first - those are the ones that should be the oldest. So when the update happens, the newest node gets the oldest time, and second newest gets the second oldest, ... all the way until the oldest node gets the newest time.

And so they end up with reversed created times, and the view will now show them in the correct order.