I've got a little time-tracking web app (implemented in Rails 3.2.8 & MySQL). The app has several users who add their time to specific tasks, on a given date. The system is set up so a user can only have 1 time entry (i.e. row) per task per date. I.e. if you add time twice on the same task and date, it'll add time to the existing row, rather than create a new one.

Now I'm looking to merge 2 tasks. In the simplest terms, merging task ID 2 into task ID 1 would take this

time  | user_id  | task_id  | date
------+----------+----------+-----------
10    | 1        | 1        | 2012-10-29
15    | 2        | 1        | 2012-10-29
10    | 1        | 2        | 2012-10-29
5     | 3        | 2        | 2012-10-29

and change it into this

time  | user_id  | task_id  | date
------+----------+----------+-----------
20    | 1        | 1        | 2012-10-29 <-- time values merged (summed)
15    | 2        | 1        | 2012-10-29 <-- no change
5     | 3        | 1        | 2012-10-29 <-- task_id changed (no merging necessary)

I.e. merge by summing the time values, where the given user_id/date/task combo would conflict.

I figure I can use a unique constraint to do a ON DUPLICATE KEY UPDATE ... if I do an insert for every task_id=2 entry. But that seems pretty inelegant.

I've also tried to figure a way to first update all the rows in task 1 with the summed-up times, but I can't quite figure that one out.

Any ideas?