Merging rows with uniqueness constraints
- by Flambino
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?
