MySQL: shift all dates forward so max date = now

Question

Here is a sample of my results MySQL table:

I would like to shift forward all dates for a specific user by the same interval, so that the highest date for that user is the current timestamp. I know how to get the interval in days:

/* result is 823 */
SELECT DATEDIFF(
   CURDATE(),
   (SELECT MAX(r.`LastReviewed`) 
    FROM `results` r
     WHERE r.`UserID` = 1)
)

But I don't know how to use that information in the greater query that would shift dates forward. I've tried:

UPDATE `results` r
SET r.`LastReviewed` = 
        r.`LastReviewed` + 
        INTERVAL (
            SELECT DATEDIFF(
               CURDATE(),
               (SELECT MAX(r.`LastReviewed`) 
                FROM `results` r
                 WHERE r.`UserID` = 1)
            )
        ) DAY
WHERE r.`UserID` = 1

But this errors with:

Error Code 1093: You can't specify target table 'r' for update in FROM clause

2nd problem is that even if it worked, it runs the risk of shifting the max record into the future if it occurs near the end of the day (11:59 pm). I'd like the new max to be the current datetime

SQL Fiddle

Answer 1

You can do it with two consecutive statements.

SELECT @offset := DATEDIFF(
   CURDATE(),
   (SELECT MAX(LastReviewed) 
    FROM results
     WHERE UserID = 1)
);
UPDATE results
   SET LastReviewed = LastReviewed + INTERVAL @offset DAY
 WHERE UserID = 1;

Edit Oh, you have to do it for everybody, eh? Let's use a temporary table. (These guys disappear when you're done with them.)

 CREATE TEMPORARY TABLE offsets
 SELECT DATEDIFF(CURDATE(),MAX(LastReviewed)) offset,
        UserId  
   FROM results
  GROUP BY UserId;

 UPDATE results
   JOIN offsets ON results.UserId = offsets.UserId
    SET results.LastReviewed = results.LastReviewed + INTERVAL offsets.offset DAY;

The temp table contains one row for each user. It overcomes the restriction that you can't do a summary query (MAX() in your case) in an update statement that updates the table.