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
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.