Updating SQL table values dependent on a value from an alternative table

I'll try and make this as clear as I possibly can. My structure is something like this:

Tables:

Fish
Pond
Plant
order_details
order_item
etc...

Now what I'm trying to do is this. If an order is started but not completed within half an hour the items from that order are returned to the relevant tables. I have the ordering process set up (I have not used transaction, I just insert the order items into the order_item table).

So basically I'm saying that if order_id 1 had an order_date (date-time field) more than 3 minutes ago. Then for each item on the table update the relevant stock item based on its stock_id (i.e. fish0 will be on the fish table and plant1 will be on the plant table). I guess in pseudo code it would read something like this:

select all order_id's 
from order_details 
where the datetime is more than 30 minutes ago

for each of the items with that order_id in the Order_item table

If stockid contains fish then update fish_in_stock with current qty + order qty where the stock id matches, but if the开发者_StackOverflow中文版 stock id is like pond then do the same on the pond table

Does this make sense? It may be something impossible. The only reason I ask is so that I can try and automate this as a stored procedure.

I think I understand what you are saying and I'm sure you could write a procedure to update the tables like that, but you would have to run it almost every minute to keep the fish and pond tables up to date.

To reduce the frequency of the procedure you could set up pending sale tables or shopping cart tables that hold those values until the transactions are complete. Then have a procedure to delete the transactions older than 30 minutes from the pending sale tables every 30 minutes. When you query for stock values you could have two values available. One from the main stock table and one from the pending sales tables and only include those from the pending sales tables that are 30 minutes old or less. This would allow a buffer for the frequency of the procedure that deletes the old transactions from the pending sales tables.

Not sure if that helps you, but I think this is how I would do it.

Rashlien's solution is good, and extensible. Your goal is to accept orders without "overbooking", i.e. selling items not (yet) in stock. What you simply need is either a way to delete -- or better yet ignore -- orders that are not finalized within the time allotted, when calculating quantity on hand.

The only thing about the solution that doesn't sit right with me is the word "delete". :-)

Rather than delete data from the orders table, you could flag the pending order and its line-item as "pending" or "shipped" or "expired". Then you have a way to measure orders that were never completed and perhaps investigate the reason. If the database is tied to a website, maybe it's related to problems with a particular browser, or web-page flow. The number of expired orders should be a fraction of completed orders, so you don't really have to worry about deadwood clogging your database. If it's more than a fraction, then maybe you would be glad to have saved the data to detect patterns.

I'm not able to add comment to your post, but here is what I see

--update your order_details first
Update Order_Details set Order_Status = 'CANCELLED' where Order_Date < DATEADD(MINUTE, -15, SYSDATETIMEOFFSET()) AND Order_Status = 'OPEN'

--update the quantities second add select and () for each

Update Fish set Fish_In_Stock = (Select a.Fish_In_Stock + b.Order_Item_Qty from Fish as a, Order_Item as b, Order_Details as c where c.Order_Date < DATEADD(MINUTE, -15, SYSDATETIMEOFFSET()) and b.Stock_ID = a.Stock_ID AND b.Order_Status = 'OPEN' AND c.Order_Status = 'OPEN');

Update Plant set Plant_In_Stock = (select a.Plant_In_Stock + b.Order_Item_Qty from Plant as a, Order_Item as b, Order_Details as c where c.Order_Date < DATEADD(MINUTE, -15, SYSDATETIMEOFFSET()) and b.Stock_ID = a.Stock_ID AND b.Order_Status = 'OPEN' AND c.Order_Status = 'OPEN');

Update Pond set Pond_In_Stock = (select a.Pond_In_Stock + b.Order_Item_Qty from Pond as a, Order_Item as b, Order_Details as c where c.Order_Date < DATEADD(MINUTE, -15, SYSDATETIMEOFFSET()) and b.Stock_ID = a.Stock_ID AND b.Order_Status = 'OPEN' AND c.Order_Status = 'OPEN');

Update Food set Food_In_Stock = (select a.Food_In_Stock + b.Order_Item_Qty from Food as a, Order_Item as b, Order_Details as c where c.Order_Date < DATEADD(MINUTE, -15, SYSDATETIMEOFFSET()) and b.Stock_ID = a.Stock_ID AND b.Order_Status = 'OPEN' AND c.Order_Status = 'OPEN');

Update Pump set Pump_In_Stock = (select a.Pump_In_Stock + b.Order_Item_Qty from Pump as a, Order_Item as b, Order_Details as c where c.Order_Date < DATEADD(MINUTE, -15, SYSDATETIMEOFFSET()) and b.Stock_ID = a.Stock_ID AND b.Order_Status = 'OPEN' AND c.Order_Status = 'OPEN');

--not sure what this is for or what you are trying to do here, you should have a way to join the tables or use and outer join to select those that aren't equal to anything

Update Order_Item set Order_Status = b.Order_Status FROM Order_Item as a, Order_Details as b where b.Order_Date < DATEADD(MINUTE, -15, SYSDATETIMEOFFSET()) AND a.Order_Status <> b.Order_Status