CODEWITHSUNDEEP
mysqlsql
SirBT
SirBT

Reputation: 1698

Why doenst my Update query have an effect on my table content?

I have a table that depicts future flight arrival times. After each flight arrival time has past (or expired) I would like the query to update the arrival time by inserting the next arrival time, which is usually a week from the current day/time.

Although my query doesn't yield any error messages, it doesn't have an effect on expired arrival times as it should.

Find below the query:

// The update should happen between 31 minutes to and hour after the flight has arrived.

UPDATE flightSched
SET `arrivalTime`= ADDDATE(NOW(), INTERVAL 1 WEEK)
WHERE arrivalTime BETWEEN SUBTIME(NOW(), '00:31:00')
                 AND SUBTIME(NOW(),'01:00:00')
                 ORDER BY arrivalTime ASC

Find below a simlpe version of my table:

+-------------+---------------------+
| FlightNo    | arrivalTime         | 
+-------------+---------------------+
| ET821       | 2013-11-24 08:05:24 |
| MS838       | 2013-11-24 10:05:24 |
| H7361       | 2013-11-24 13:15:06 |
+-------------+---------------------+

If the current time is 09:45, then flight number ET821 arrival time should be updated to 2013-12-01 08:05:24, Please help me understand why this update doesn't happen?

Upvotes: 1

Views: 40

Answers (1)

peterm
peterm

Reputation: 92815

Your update doesn't produce any results because your date range in WHERE clause is incorrect. The upper value in BETWEEN operator is less than the lower value.

According to your requirements instead of 

BETWEEN SUBTIME(NOW(), '00:31:00') AND SUBTIME(NOW(),'01:00:00')
                                       ^^^^^^^

it should be at least

BETWEEN SUBTIME(NOW(), '00:31:00') AND ADDTIME(NOW(),'01:00:00')
                                       ^^^^^^^

Try it the other way

UPDATE flightSched
   SET arrivalTime = arrivalTime + INTERVAL 1 WEEK
 WHERE arrivalTime >= NOW() - INTERVAL 31 MINUTE
   AND arrivalTime <= NOW() + INTERVAL 1 HOUR

Note: make sure that you have an index on arrivalTime column.

Here is SQLFiddle demo

Upvotes: 2

Related Questions