Recursive "ON DUPLICATE KEY UPDATE"

I have a database table with a UNIQUE key on a date column so that there can never be more than one row per date. If I insert a new row for a day that already exists in the database, I’d like it to push all the subsequent, consecutive dates back by one day until it reaches a ‘free’ day.

Here’s what I thought:

INSERT INTO
 `activity`
 (`name`,`date`)
VALUES
 ('date 7','2009-07-31')
ON DUPLICATE KEY
 UPDATE `date` = DATE_ADD(`date`, INTERVAL '1' DAY)

My thinking is that the ON DUPLICATE KEY UPDATE will bubble up the table and keep adding one day to every date until it reaches a day that doesn’t exist.

E.g., if my table content looks like this:

date 1, 2009-07-30
date 2, 2009-07-31
date 3, 2009-08-01
date 4, 2009-08-02
date 5, 2009-08-04 <- notice this date is two days past the previous one

…and I go to insert ‘date 7′ on 2009-07-31, I’d like it to do this:

date 1, 2009-07-30
date 7, 2009-07-31 <- newly inserted row
date 2, 2009-08-01 <- this and subsequent 开发者_高级运维rows are all incremented
                      by one day until it hits a non-consecutive day
date 3, 2009-08-02
date 4, 2009-08-03
date 5, 2009-08-04 <- notice this date hasn't changed

But on duplicate key update doesn’t work like that, it only updates the clashing row, and if that row then clashes with a different row, it bails out with a duplicate key error.

Is there a smart way to achieve this effect in SQL alone?

Query for the first free date on or after your target date. This involves a left self-join to find dates with no successor in the table.

SELECT DATE_ADD(Min(a.`date`), INTERVAL '1' DAY) AS `free_date`
FROM `activity` a
LEFT JOIN `activity` z
ON z.`date` = DATE_ADD(a.`date`, INTERVAL '1' DAY)
WHERE z.`date` IS NULL 
AND a.`date` >= '2009-07-31'

Run an update to increment each date in the range between your target date and the first free date.

Now there is room for your desired insert.

select the max date before doing the update, then add one day to the max

something like that:

ON DUPLICATE KEY

 UPDATE `date` = DATE_ADD(select max('date') from 'activity', INTERVAL '1' DAY)