MySQL update from select and further select and updates

Question

Currently trying to find a way to do the following inside some form of loop (preferably without a performance hit on database).

I have 3 tables user_hours, user_calendar and hours_statistics. I need to first do:

SELECT user_calendar.date_start,
       user_calendar.opportunity_id,
       user_hours.user_id,
       user_hours.agreed_hours,
       user_hours.completed_hours,
       user_hours.hours_committed
FROM   user_calendar
       JOIN user_hours
         ON user_calendar.user_calendar_id = user_hours.user_calendar_id
WHERE  user_calendar.date_start = CURRENT_DATE()
       AND user_hours.completed_hours IS NULL
       AND user_hours.hours_committed = 'accepted'

This query could return like the following:

https://i.stack.imgur.com/FAV61.png

So for each opportunity_id and user_id returned i'd like to then do:

UPDATE user_hours
SET    completed_hours = agreed_hours,
       hours_committed = 'completed'
WHERE  opportunity_id = {opportunity_id}
       AND user_id = {user_id}
       AND hours_committed = 'accepted'
       AND completed_hours IS NULL

Note that {opportunity_id} and {user_id} would need to be looped at this point (see screenshot) because we need to go through each user on each opportunity.

Then for each updated record i'd need to then get the total hours like:

// Get hours they have done to send to statistics data table
SELECT sum(completed_hours) FROM user_hours WHERE user_id = {user_id} AND opportunity_id = {opportunity_id} 

// Get the completed hours total somehow as a variable
$completed_hours = (from result above)

// Commit stats
UPDATE hours_statistics SET completed_hours = (completed_hours+$completed_hours)
WHERE user_id = {user_id} AND opportunity_id =  {opportunity_id}

Could anyone help write this as a procedure or a trigger of some kind or help me in the right direction to get a starting point for looping over this stuff? Manually the querying works, just need to be looped / automatic for a stats update to run.

I was hoping for a straight MySQL solution on this, forloop on this would potentially create too much overhead? I am open to all suggestions on how best to run this, some people say MySQL Schedule others PHP, i'm open to go with the best method — MarkH, Aug 26 '12 at 10:57

score 2 · Accepted Answer · answered Aug 26 '12 at 11:01

2

You can create a trigger to update hours_statistics whenever user_hours is updated (you may also want to add similar triggers for INSERT and DELETE operations, depending on your application logic).

Assuming that a UNIQUE key has been defined on hours_statistics.(user_id, opportunity_id) one can use INSERT ... ON DUPLICATE KEY UPDATE within the trigger:

CREATE TRIGGER foo AFTER UPDATE ON user_hours FOR EACH ROW
  INSERT INTO hours_statistics (user_id, opportunity_id, completed_hours) VALUES
    (OLD.user_id, OLD.opportunity_id, -OLD.completed_hours),
    (NEW.user_id, NEW.opportunity_id, +NEW.completed_hours)
  ON DUPLICATE KEY UPDATE
    completed_hours = completed_hours + VALUES(completed_hours);

Then you can use a single UPDATE statement (using the multiple-table syntax to join user_hours with user_calendar) to perform all of the updates on user_hours in one go, which will cause the above trigger to update hours_statistics as desired:

UPDATE user_hours JOIN user_calendar USING (user_calendar_id, opportunity_id)
SET    user_hours.completed_hours = agreed_hours,
       user_hours.hours_committed = 'completed'
WHERE  user_hours.hours_committed = 'accepted' 
   AND user_hours.completed_hours IS NULL
   AND user_calendar.date_start = CURRENT_DATE();

answered Aug 26 '12 at 11:01

eggyal

122,705
18
212
237

Not quite right its inserting some duplicates to the hours_statistics but this is a great start, could you help debug this further? Also what if i needed another trigger for some other similar scenario? – MarkH Aug 26 '12 at 11:10
Did you see the paragraph "*Assuming that a `UNIQUE` key has been defined on `hours_statistics.(user_id, opportunity_id)` ...*"? Have you defined such a `UNIQUE` key? You'll have to clarify what your other similar scenario is, and what problems you're having adapting the above to it. – eggyal Aug 26 '12 at 11:11
which would we set as the UNIQUE key? – MarkH Aug 26 '12 at 11:16
You need to define a composite `UNIQUE` key over both columns. – eggyal Aug 26 '12 at 11:17
Could you give me an SQL to do this please? – MarkH Aug 26 '12 at 11:18
`ALTER TABLE hours_statistics ADD UNIQUE KEY (user_id, opportunity_id)` – eggyal Aug 26 '12 at 11:19
I added: ALTER TABLE hours_statistics ADD UNIQUE KEY (user_id, opportunity_id); – MarkH Aug 26 '12 at 11:20
Now the completed_hours will not insert? any ideas? also what about on other days we run the query, will this update the total hours on the unique record? – MarkH Aug 26 '12 at 11:21
What do you mean "*the completed_hours will not insert*"? Are you getting an error? And yes, `INSERT ... ON DUPLICATE KEY UPDATE` updates the record (rather than inserting a new one) if the `UNIQUE` key already exists in the table. – eggyal Aug 26 '12 at 11:23
i am running the following and the completed_hours is NULL after the update in hours_statistics: UPDATE user_hours JOIN user_calendar USING (user_calendar_id, opportunity_id) SET user_hours.completed_hours = agreed_hours, user_hours.hours_committed = 'completed' WHERE user_hours.hours_committed = 'accepted' AND user_hours.completed_hours IS NULL AND user_calendar.date_start = CURRENT_DATE(); – MarkH Aug 26 '12 at 11:24
user_calendar structure: http://i.imgur.com/zIDdm.png user_hours structure: http://i.imgur.com/CUbOD.png hours_statistics structure: http://i.imgur.com/sF0pf.png – MarkH Aug 26 '12 at 11:37
One other small thing, prevention of the trigger on update if it is not for that day? at the moment if admin amends a record it auto updates stats if that day is not in the current day, anyway to prevent that? Thanks in advance. – MarkH Aug 26 '12 at 14:16
PS. Literally only want this to trigger based on being ran ONCE in a current_date() if other days user_hours are altered / updated we dont want to do a trigger. Hope this makes sense, hope someone can help – MarkH Aug 26 '12 at 14:36

MySQL update from select and further select and updates

1 Answers1