BeetleJuice BeetleJuice - 2 months ago 7
MySQL Question

MySQL: shift all dates forward so max date = now

Here is a sample of my

results
MySQL table:

enter image description here

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

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;
Comments