January 30, 2014 at 3:21 pm
It was requested that I update my question by inserting the sample data in a way that could be utilized to help me with my query. So I am redoing the question which can be found at:
http://www.sqlservercentral.com/Forums/Topic1534827-391-1.aspx
I have also modified the text of the question. The sample data follows my explanation.
I have a complicated update query involving values in 3 hierarchical tables. The problem involves updating the 3rd table (storeproducts) with data from the same table that can be determined only by linking to the second table (StoreList) which is linked to the top table (Grouptable). I think the link to the GroupTable is not since I can access the GroupID separately in the query before doing the main procedure.
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 StoreList links to multiple rows in table3, Storeproducts via its primary key, ListID. For each ListID 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 and a person calls each store each week to find out the products desired. So each Monday a set of the same stores each with the same group of products is created.
On first Monday when the set is created, the Grouptable will hold a new GroupID (groupid = 1)
which will link to the StoreList Table. The StoreList Table will have the list of storeID’s. The StoreProductsTable will access the stores by linking to the StoreID in the List table via the ListID. The StoreProducts table does not hold the StoreID but has the List table’s primary key for that StoreID record.
So for GroupID = 1 in the example, there are 2 rows (ListID: 11, 12) consisting of storeID: 4, 5 . The StoreProducts table rows in the sample show this linkage.
On second Monday, a new set will be created with GroupID = 2. The same group of stores each with the same group of products will be created. So for GroupID = 2 (for the second Monday) There are 2 records also for storeID 4 and 5 in the StoreList Table.
When the new set (groupid = 2) is created, the PrevQty fields in the StoreProducts table (table 3) needs to be updated with the Quantity field from the first Monday (Group 1) so people can see what was ordered last week. Quantity will hold the number of each product that is wanted and can be zero. This will be done from each store and the quantity of each of the products in that store for the first Monday (groupid = 1) will be placed in the PrevQty field for the same store and the same products for the second Monday (Group 2).
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 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. This eliminates the serious join problems. The only examples I have found for this are solutions for separate parts of the problem: updating a table with values in same table (easy enough) or updating values in a table with a join to another table. Here, it is necessary for storeproducts table to link to StoreList table to get the storeID so it can put the qty form the one store, one product for group 1 into the same store, same product for group2
Any thoughts greatly appreciated.
If it is necessary I can send you the 2 temp tables I would create that would hold all the necessary fields for First Monday in 1 table and the necessary fields for Second Monday in a second table. Then I could more easily link the two tables , update the prevQty in Second Monday table with the Qty from the first Monday table. Then using the primary key of the StoreProducts table, update the actual table.
There are 14 stores each with 730 products.
Here is the sample data.
--===== Table 1 of 3. If the test table GroupTbl already exists, drop it
IF OBJECT_ID('TempDB..#GroupTbl','U') IS NOT NULL
DROP TABLE #GroupTbl
--===== Create the test GroupTbl table with
CREATE TABLE #GroupTbl
(
GroupID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #GroupTbl ON
--===== Insert the test data into the test GroupTbl table
INSERT INTO #GroupTbl
(GroupID)
SELECT 1 UNION ALL
SELECT 2
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #GroupTbl Off
--===== Table 2 of 3. If the test table StoreList already exists, drop it
IF OBJECT_ID('TempDB..#StoreList','U') IS NOT NULL
DROP TABLE #StoreList
--===== Create the test table StoreList with
CREATE TABLE #StoreList
(
ListID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
GroupID INT not null default 0 ,
StoreID INT not null default 0
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #StoreList ON
--===== Insert the test data into the test table StoreList
INSERT INTO #StoreList
(ListID, GroupID, StoreID)
SELECT 11, 1, 4 UNION ALL
SELECT 12, 1, 5 UNION ALL
SELECT 14, 2, 4 UNION ALL
SELECT 15, 2, 5
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #StoreList Off
--===== Table 3 of 3. If the test table StoreProducts already exists, drop it
IF OBJECT_ID('TempDB..#StoreProducts','U') IS NOT NULL
DROP TABLE #StoreProducts
--===== Create the test table StoreProducts with
CREATE TABLE #StoreProducts
(
StoreProdID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ListID INT not null default 0 ,
ProdID INT not null default 0,
Qty INT not null default 0 ,
PrevQty INT not null default 0
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #StoreProducts ON
--===== Insert the test data into the test table StoreProducts
INSERT INTO #StoreProducts
(StoreProdID, ListID, ProdID, Qty, PrevQty)
SELECT 1, 11, 8, 7, 0 UNION ALL
SELECT 2, 11, 9, 3, 0 UNION ALL
SELECT 4, 12, 8, 4, 0 UNION ALL
SELECT 5, 12, 9, 0, 0 UNION ALL
SELECT 10, 14, 8, 0, 0 UNION ALL
SELECT 11, 14, 9, 0, 0 UNION ALL
SELECT 13, 15, 8, 0, 0 UNION ALL
SELECT 14, 15, 9, 0, 0
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #StoreProducts Off
January 30, 2014 at 3:35 pm
Excellent job posting ddl and sample data. I have tried to read the explanation of what you are trying to do and it is just making my head spin. Can you try explain clearly what you want the update to do? And what should your sample data look like after this update?
_______________________________________________________________
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/
January 30, 2014 at 4:09 pm
My head has not spun this much since Hugo posted that "HAVING EXISTS" query 3 years ago! :hehe:
January 30, 2014 at 4:22 pm
Yes, I totally understand. I am constantly going back to the table designs for this project and have drawn up my own schema that I constantly refer to. (there are many other tables connected here and more fields than I list). Plus an update query to a remote server giving me nightmares. I should submit this project to those brain exercise web sites. I have myself gotten to the point where I can hold many of the table links in my working memory. Thinking about this is actually good for insomnia too.
I will work on some result samples to make it clearer.
January 30, 2014 at 4:38 pm
yep, that data structure is really confusing
Can you give an example of the expected output?
Or would the insert go into the #storeproducts table?
try this and let me know if it helps at all, if so you would just have to add your insert statments
;
WITH previousweek
AS ( SELECT StoreProdID
, sp.ListID
, ProdID
, Qty
, PrevQty
, GroupID
, StoreID
FROM #StoreProducts sp
JOIN #StoreList sl
ON sl.ListID = sp.ListID
) ,
thisweek
AS ( SELECT StoreProdID
, sp.ListID
, ProdID
, Qty
, PrevQty
, GroupID
, StoreID
FROM #StoreProducts sp
JOIN #StoreList sl
ON sl.ListID = sp.ListID)
SELECT tw.ListID
, tw.ProdID
, tw.Qty
, pw.qty AS PrevQty
FROM previousweek pw
JOIN thisweek tw
ON pw.ProdID = tw.ProdID
AND pw.StoreID = tw.StoreID
AND tw.GroupID - 1 = pw.GroupID
January 30, 2014 at 4:42 pm
let me try this again
;
WITH newtable
AS ( SELECT StoreProdID
, sp.ListID
, ProdID
, Qty
, PrevQty
, GroupID
, StoreID
FROM #StoreProducts sp
JOIN #StoreList sl
ON sl.ListID = sp.ListID
)
SELECT tw.ListID
, tw.ProdID
, tw.Qty
, pw.qty AS PrevQty
FROM newtable tw
JOIN newtable pw
ON pw.ProdID = tw.ProdID
AND pw.StoreID = tw.StoreID
AND tw.GroupID - 1 = pw.GroupID
January 30, 2014 at 5:49 pm
The first answer is the solution. (WITH previousweek...) Terrific. The updates do go into the storeproducts table which holds the list of products for each store in each group of stores (A group consists of all stores in a particular state). (A callsheet is prepared each week for each group of stores. Someone calls each store for the quantity of product they want that week and then they are delivered). So GroupID in the sample really referred to the set call sheets for all the stores in one area for a particular week. GroupID = 1 is the set of callsheets for the particular stores for the first Monday. Group 2 is the callsheet forthe set of the same stores and same products for the second Monday. So each week the person making the calls wants to see what the store ordered last week for each product. The storeproduct table links to the "StoreList" (in the sample) and from there can link to a Stores Table to get the real store name. The productID in the storeproducts table links to a product table where the product descriptions, etc. can be gotten. A select query creates the grid which is the call sheet.
I have attached a sample callsheet (it has 730 products actually). This shows the finished product and shows how the results of the query fit in. The first column, quantity, is what they enter each week and next to it is the previous quantity from this query. There is a good reason why the storeID is not in the Storeproducts table, but it is not necessary to know.
I need to put this into the actual table names and run to make sure it will work with all the other things added. I can also flesh out the sample tables to show the store, product information. I realize that just having ID's in the sample is not very helpful but I did not want to add to the complexity -- though I think it would have made it simpler had I done so. So I will add the additional fields to the three tables.
Let me know if there is other information to make it clearer. I am hoping this could function as a good example though it has to be more clearly explained. As I said, I found samples of updating to the same table and updating to a table where a join is needed to one or more other tables, but I did not see the two solutions in one query and I could not figure how to do that. This is a great lesson. Thank you very much for the tips on describing the problem and the solution itself.
January 31, 2014 at 9:24 am
The first and the second query should give you the same results.
I just did some consolidation. All the "magic" is in the "on" criteria.
Pretty sure it can be done without the CTE and possibly without subquerys but I can't wrap my head around that at the moment since I've forced myself to always think in CTEs. haha
As for adding an update statement, try this:
;
WITH previousweek
AS ( SELECT StoreProdID
, sp.ListID
, ProdID
, Qty
, PrevQty
, GroupID
, StoreID
FROM #StoreProducts sp
JOIN #StoreList sl
ON sl.ListID = sp.ListID
) ,
thisweek
AS ( SELECT StoreProdID
, sp.ListID
, ProdID
, Qty
, PrevQty
, GroupID
, StoreID
FROM #StoreProducts sp
JOIN #StoreList sl
ON sl.ListID = sp.ListID)
UPDATE tw SET tw.prevqty=pw.qty
FROM previousweek pw
JOIN thisweek tw
ON pw.ProdID = tw.ProdID
AND pw.StoreID = tw.StoreID
AND tw.GroupID - 1 = pw.GroupID
January 31, 2014 at 1:56 pm
Hi, yes all three work and the final updates into the #storeproducts table. Thank you again. Using CTE in this case shows clearly what the logic of the procedure is and I would think is easier to modify. What are the downsides of this method? I think without sacrificing performance, if it can be more readable, it is better to go that route.
January 31, 2014 at 2:42 pm
Actually CTEs tend to be better for performance.
Downsides are that they can be difficult to learn at first.
They can reference themselves which is awesome but you can get stuck in a loop.
Sometimes you may want to break them up so you won't hog up memory.
If you do recursion there is a limit for certain variables.
That's all I can think of but I am sure there is more.
January 31, 2014 at 2:57 pm
stmu009 (1/31/2014)
Actually CTEs tend to be better for performance.
Better for performance than what?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2014 at 3:05 pm
Definitely better than using cursors.
I lean toward them more than subqueries.
I prefer them over using temp tables in most cases.
I can probably do a quick google search for some examples.
They are not better for performance 100% of the time, especially if they are not well written, so IT'S ALWAYS BEST TO TEST.
February 17, 2014 at 11:19 pm
stmu009 (1/31/2014)
Definitely better than using cursors.I lean toward them more than subqueries.
I prefer them over using temp tables in most cases.
I can probably do a quick google search for some examples.
They are not better for performance 100% of the time, especially if they are not well written, so IT'S ALWAYS BEST TO TEST.
While I agree that would be true of set-based CTEs, Recursive CTEs are frequently worse than a well written Cursor especially those that "count" or process one row at a time. Behind the scenes, Recursive CTEs are some pretty nasty loops.
CTEs also have a problem when self joined or used in a FROM clause more than once. Just like a View, they are totally re-executed for each instance in the FROM clause or in cascading CTEs. In such cases, it would be better to store the interim results in a Temp Table and then join to the Temp Table many times.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 18, 2014 at 1:49 pm
Wouldn't mind seeing a sample of a worst case CTE and a solution with a temp table, for example.
February 18, 2014 at 9:52 pm
smhaig (2/18/2014)
Wouldn't mind seeing a sample of a worst case CTE and a solution with a temp table, for example.
Just write a nice little 30 table query into a CTE and join it to itself for a dupe check. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply