January 26, 2014 at 3:59 pm
I have a complicated update query involving values in 3 hierarchical tables. The top table 1, grouptable, has a primary key groupID which is linked to the second table in a one to many relationship. The 2nd table StoreListTable links to multiple rows in table3, Storeproducts via its primary key, StoreListID. For each StoreListID in the StoreProducts table there is a list of productID’s.
The purpose of these tables is to set up a delivery list of products for different stores for different store dates. So each Monday a set of the same stores each with the same group of products is created. Here is a sample with the update problem.
On Monday 1 when the set is created, the Grouptable will hold a new GroupID (groupid = 1)
which will link to the StoreListTable. The StoreListTable will have the list of storeID’s
So for GroupID = 1 in the example, there are 2 rows (ListID: 11, 12) consisting of storeID: 4, 5 for StoreProducts, i.e. table 2.
On Monday 2, a new set will be created with GroupID = 2. The same group of stores each with the same group of products will be created. When the new set is created, the PreviousQuantity fields in the StoreProducts table (table 3) needs to be updated with the Quantity field from Monday 1 so people can see what was ordered last week. This will be done from each store and the products in that store for Monday 1 to the same store and the same products in that store for Monday 2. Here is a diagram.
Say Monday 1 groupID = 1 and the Monday 2 has groupID = 2. The storelist table (table 2) is linked to each groupID as follows:
ListID|GroupID|StoreID ---- groupID = 1
11|1|4
12|1|5
ListID|GroupID|StoreID ---- GroupID = 2
14|2|4
15|2|5
------------------------------------------------------------
Table 3 (storeProductsTable) is as follows
for groupID = 1. Note that the ListID points to the StoreID in the StoreListtable so both ListID 11 (groupID 1) and ListID 14 (groupID 2) point to the same store: 4. Each store has the same list of products for all stores in group 1 and 2 but the quantities ordered will be different.
storeprodID|ListID|ProdID|Qty|PrevQty ---
------------------------------
1|11|8|7|2
2|11|9|3|0
4|12|8|4|5
5|12|9|2|5
7|13|8|0|1
8|13|9|3|0
------------------------------
storeprodID|ListID|ProdID|Qty|PrevQty - the group2 records the prevqty will be updated to the qty in GroupID = 1.
10|14|8| 0|0
11|14|9 |0|0
13|15|8| 0|0
14|15|9 |0|0
16|16|8| 0|0
17|16|9 |0|0
The only way I can see doing this is either to loop thru the tables in SqL or using a .net dataset that calls a stored procedure (not desired) or creating 2 table variables in a stored procedure table from a select query with all the necessary values from the different tables in each of the 2 tables, updating that and then placing the values into the actual table. The only examples I have found for this are solutions for separate parts of the problem: updating a table with values in same table or updating values in a table with a join to another table.
Any thoughts greatly appreciated.
January 27, 2014 at 1:52 am
January 27, 2014 at 11:10 am
So Sorry. Will need some time to put it all together. Thanks for letting me know.
January 30, 2014 at 3:33 pm
This discussion has moved to a new thread...http://www.sqlservercentral.com/Forums/Topic1536570-391-1.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply