LovinSpoonful
asked on
Update a single row & column in one table with values from a different table's multiple rows and columns
Hello Experts:
This is a hard one.
I have a table called INV that has columns ItemNo, Whse, Locator, QtyOnHand, LastUpdateDate. There are many records for each ItemNo.
I need to post a consolidated string from INV into BOM, matching on BOM.ItemNo = INV.ItemNo.
I need three rows of three columns each from INV to update 1 row, 1 column in BOM.
Specifically, on each matching ItemNo, I need the first 3 rows from INV, columns "Whse, Locator, QtyOnHand" consolidated into a single string and posted into BOM.InvDetails.
I tried this:
UPDATE b SET InvDetails =
(select * from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum1 from INV where itemNo = b.ItemNo ) a1 where rnum1 = 1)
+
(select * from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum2 from INV where itemNo = b.ItemNo ) a2 where rnum2 = 2)
+
(select * from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum3 from INV where itemNo = b.ItemNo ) a3 where rnum3 = 3)
FROM BOM b
I GET THIS ERROR: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
I know how to do this procedurally using a sequence of queries in a loop but that takes too long and I'm trying to learn how to do this within a single SQL transaction. So Please, do not send me any examples of using a do/loop or for/each, etc.
If anyone could send a working TSQL example to me, that would be really great!!!
Thanks in advance.
This is a hard one.
I have a table called INV that has columns ItemNo, Whse, Locator, QtyOnHand, LastUpdateDate. There are many records for each ItemNo.
I need to post a consolidated string from INV into BOM, matching on BOM.ItemNo = INV.ItemNo.
I need three rows of three columns each from INV to update 1 row, 1 column in BOM.
Specifically, on each matching ItemNo, I need the first 3 rows from INV, columns "Whse, Locator, QtyOnHand" consolidated into a single string and posted into BOM.InvDetails.
I tried this:
UPDATE b SET InvDetails =
(select * from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum1 from INV where itemNo = b.ItemNo ) a1 where rnum1 = 1)
+
(select * from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum2 from INV where itemNo = b.ItemNo ) a2 where rnum2 = 2)
+
(select * from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum3 from INV where itemNo = b.ItemNo ) a3 where rnum3 = 3)
FROM BOM b
I GET THIS ERROR: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
I know how to do this procedurally using a sequence of queries in a loop but that takes too long and I'm trying to learn how to do this within a single SQL transaction. So Please, do not send me any examples of using a do/loop or for/each, etc.
If anyone could send a working TSQL example to me, that would be really great!!!
Thanks in advance.
;WITH CTE AS (
select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand) AS invDetails
, ROW_NUMBER() OVER (PARTITION BY itemNo ORDER BY lastupdatedate) rnum
from INV
)
UPDATE b
SET InvDetails= (SELECT ISNULL(MAX(invDetails),'') FROM CTE WHERE itemNo=b.itemNo AND rnum=1)
+(SELECT ISNULL(MAX(invDetails),'') FROM CTE WHERE itemNo=b.itemNo AND rnum=2)
+(SELECT ISNULL(MAX(invDetails),'') FROM CTE WHERE itemNo=b.itemNo AND rnum=2)
FROM BOM b
Correction:
For Line 9: rnum = 2 should be rnum=3
For Line 9: rnum = 2 should be rnum=3
UPDATE b SET InvDetails =
(select invDetails from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum1 from INV where itemNo = b.ItemNo ) a1 where rnum1 = 1)
+
(select invDetails from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum2 from INV where itemNo = b.ItemNo ) a2 where rnum2 = 2)
+
(select invDetails from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum3 from INV where itemNo = b.ItemNo ) a3 where rnum3 = 3)
FROM BOM b
(select invDetails from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum1 from INV where itemNo = b.ItemNo ) a1 where rnum1 = 1)
+
(select invDetails from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum2 from INV where itemNo = b.ItemNo ) a2 where rnum2 = 2)
+
(select invDetails from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum3 from INV where itemNo = b.ItemNo ) a3 where rnum3 = 3)
FROM BOM b
its because you have select * in your subqueries ... you can only return a single column...
UPDATE b SET InvDetails =
(select invdetails from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum1 from INV where itemNo = b.ItemNo ) a1 where rnum1 = 1)
+
coalesce((select invdetails from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum2 from INV where itemNo = b.ItemNo ) a2 where rnum2 = 2) ,'')
+
coalesce((select invdetails from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum3 from INV where itemNo = b.ItemNo ) a3 where rnum3 = 3) ,'')
FROM BOM b
UPDATE b SET InvDetails =
(select invdetails from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum1 from INV where itemNo = b.ItemNo ) a1 where rnum1 = 1)
+
coalesce((select invdetails from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum2 from INV where itemNo = b.ItemNo ) a2 where rnum2 = 2) ,'')
+
coalesce((select invdetails from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum3 from INV where itemNo = b.ItemNo ) a3 where rnum3 = 3) ,'')
FROM BOM b
ASKER
Hello Thomasian, I really like the use of a CTE here, it's something I'm trying to understand. The query you gave me will execute but it assigns the same value to every row in b table. The "WHERE itemNo=b.itemNo" isn't working. I wonder if there is a small tweak to this we could do ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello Iuani. I tried your solution next. The problem is that it returns a null value in any cases where there aren't 3 records available for that item in the INV table.
ASKER
Hello LowfatSpread, your solution works fine. Thank you for showing me the Coalesce function. The only problem is the speed. It takes 21 seconds to execute in my db. Trying the update from Thomasian next...
ASKER
Hello Thomasian, your update works perfectly and executes in 2 seconds. Thanks for showing me how to do this. What an awesome solution.
ASKER
AWESOME!
coalesce is not causing you any speed degredation...
the query is merely a correction to the initial one you supplied...
the 21 seconds is you basic database performance "problem"
how have you tested that your selected solution actually performs significantly better...
did you clear cache , etc...
or are you benefiting from the buffers now being populated with the data already retrieved.
good luck
the query is merely a correction to the initial one you supplied...
the 21 seconds is you basic database performance "problem"
how have you tested that your selected solution actually performs significantly better...
did you clear cache , etc...
or are you benefiting from the buffers now being populated with the data already retrieved.
good luck
ISNULL will do the trick :
UPDATE b SET InvDetails =
(select isNULL(invDetails,'') from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum1 from INV where itemNo = b.ItemNo ) a1 where rnum1 = 1)
+
(select isNULL(invDetails,'') from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum2 from INV where itemNo = b.ItemNo ) a2 where rnum2 = 2)
+
(select isNULL(invDetails,'') from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum3 from INV where itemNo = b.ItemNo ) a3 where rnum3 = 3)
FROM BOM b
But I see you already choosed a solution...good luck.
UPDATE b SET InvDetails =
(select isNULL(invDetails,'') from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum1 from INV where itemNo = b.ItemNo ) a1 where rnum1 = 1)
+
(select isNULL(invDetails,'') from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum2 from INV where itemNo = b.ItemNo ) a2 where rnum2 = 2)
+
(select isNULL(invDetails,'') from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand)
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum3 from INV where itemNo = b.ItemNo ) a3 where rnum3 = 3)
FROM BOM b
But I see you already choosed a solution...good luck.
@luani
the isnull / coalesce needs to cover the whole subquery not be within it
coalesce((subquery),'')
the isnull / coalesce needs to cover the whole subquery not be within it
coalesce((subquery),'')
@Lowfatspread :
I think in his case both will work...but you are right it is better to cover the whole subquery
I think in his case both will work...but you are right it is better to cover the whole subquery
ASKER
For anyone using this solution, for the CTE solution, don't forget to correct line 9 to say "3" not "2".
ASKER