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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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:
car, which has a status that can be "reserved", "pending confirmation", or "stock" (there could be other values, we don't care right now).
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:
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.