Update statement with join and two where conditions

Question

This is what I need the update to do:

• update my summary table TIME column with the user-edited TIME value
• compare the new TIME, using ID's from my summary table and hour_interval table to join tables
• find the correct row ID where the new TIME falls within (there are two columns with start and end hours; example would be: start column - 04:00:00 end column 06:00:00)
• update the summary table with the correct interval row ID using the summary table ID

The code executes, but doesn't update the interval ID. Suggestions?

UPDATE summary S  
JOIN hour_interval H  
ON S.hourinterval_id = H.hourinterval_id 
SET S.hourinterval_id = H.hourinterval_id 
WHERE ('$new_time' BETWEEN H.start_hour AND H.end_hour) 
AND summary_id = '$summary_id'"

hourinterval_id start_hour  end_hour
1               4:00:00 5:59:59
2               6:00:00 7:59:59
3               8:00:00 9:59:59
4              10:00:00 11:59:59
5              12:00:00 13:59:59
6              14:00:00 15:59:59
7              16:00:00 17:59:59
8              18:00:00 19:59:59

Answer 1

Try this:

UPDATE summary S  
JOIN hour_interval H  
ON '$new_time' BETWEEN H.start_hour AND H.end_hour
SET S.hourinterval_id = H.hourinterval_id 
WHERE summary_id = '$summary_id'"