-1

I want update the value of a column in a table after 24 hours. We're booking a car for a customer; if he doesn't confirm within 24 hours, the car's status should automatically change to "stock".

How can I do this in SQL Server 2008?

1
1

In order to update the value after 24 hours, you'll need to have a column (probably in the same table as the car reservation) that records when the reservation was entered into the system.

Presumably, as this point, a row representing the car was updated with a status like "pending confirmation".

Similarly, when the customer confirms the reservation, presumably some value like "reserved" is set in that row.

In other words, there's a way to tell when a car is set aside for a customer, and if the customer has confirmed that reservation.

Let's assume the following:

  • There's a table, car, which has a status that can be "reserved", "pending confirmation", or "stock" (there could be other values, we don't care right now).
  • There's a table, reservation, where a particular car is reserved for a particular customer. This table includes a timestamp when the unconfirmed reservation was entered into the system.

Then, you would have a statement like the following:

UPDATE car
   SET status = 'stock'
 WHERE status = 'pending confirmation'
   AND (SELECT MAX(res_entered)
          FROM reservation res
         WHERE res.car_id = car.car_id) < DATEADD(day, (-1), GETDATE())
;

The above makes at least one notable assumption:

  • There will be only one pending reservation for a specific car at a time. If you might have one pending reservation for a specific car for 1 week from today, and another pending reservation for the same car for 3 weeks from today, then you'd need something more complicated here. (Of course, you'd need something more complicated than a row for a car that marks it as "reserved" or "stock", too).

If your situation is more complicated than can be handled by the above, you'll need to update the question with more details.

And (as I meant to include originally, but left out; thanks to Dominique Boucher for pointing it out), you would then create a SQL Server job to run this statement. You can schedule this to run as frequently as you want; if it normally completes in under a minute, you could schedule it for every minute, even. Odds are every 5-15 minutes will be adequate for most purposes, but that's up to you to determine.

Note, however, that the statement could impact performance. If there are a large number of records to update each time the job is run, then car records could be locked longer than desired; it's even possible that the DB engine could decide to lock the car table if there are enough changes in a run. There are ways to manage this if it becomes a problem, but thought I should mention it in passing, since I don't know how active the site will be.

0

And you can create a job in the SQL Agent that would run every, let say, 5 min and update all row that are in "pending confirmation" for more then 24 hours.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.