Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stefanwright1988
Starting Member
2 Posts |
 Posted - 2010-12-12 : 14:35:50
|
| Hi Everyone,I'll try and make this as clear as I possibly can do.My structure is something like this:Tables:FishPondPlantorder_detailsorder_itemetc...Now what im 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 im saying that if order_id 1, had an order_date (date-time field) more than 3 minutes ago. Then for each items 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 agofor each of the items with that order_id in the Order_item tableif stockid contains fish then update fish_in_stock with current qty + order qty where the stock id matchesbut if the stock id is like pond then do the same on the pond tabledoes this make sense?? If anyone can shed some light on how to do this I would be grateful :) It may be something impossible the only reason I ask is so that I can try and automate this as a stored procedure |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-12 : 21:06:25
|
| You hold quantities on separate tables?Why not have a tableStockID, Quantity - could have stocktype if necessary.Then you can just join to it and do the updates in one query.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
stefanwright1988
Starting Member
2 Posts |
Posted - 2010-12-13 : 04:28:21
|
| Thanks for your reply. The reason I have different tables for the fish and ponds etc is because that is how I was asked to design it. The system has already undergone most of the development so going and completely changing the table structure now would make a lot of work for something that I only have a few months left to finish (its my final year university project) |
|
|
|
|
|
|
updating variable table in a query