Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update a single row & column in one table with values from a different table's multiple rows and columns

Posted on 2011-09-28
16
Medium Priority
?
282 Views
Last Modified: 2012-05-12
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) AS invDetails,
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) AS invDetails,
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) AS invDetails,
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.


0
Comment
Question by:LovinSpoonful
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
  • +1
16 Comments
 

Author Comment

by:LovinSpoonful
ID: 36755968
I am using SQL Server 2008
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36779643
;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

Open in new window

0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36779966
Correction:

For Line 9: rnum = 2 should be rnum=3
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 7

Expert Comment

by:luani
ID: 36813013
UPDATE b SET InvDetails =

(select invDetails from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand) AS invDetails,
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) AS invDetails,
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) AS invDetails,
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum3 from INV where itemNo = b.ItemNo ) a3 where rnum3 = 3)

FROM BOM b
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36813561
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) AS invDetails,
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) AS invDetails,
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) AS invDetails,
ROW_NUMBER() OVER (ORDER BY lastupdatedate) rnum3 from INV where itemNo = b.ItemNo ) a3 where rnum3 = 3) ,'')

FROM BOM b
0
 

Author Comment

by:LovinSpoonful
ID: 36814446
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 ?
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 2000 total points
ID: 36814684
You can read about CTEs in http://msdn.microsoft.com/en-us/library/ms190766.aspx . If there's anything you don't understand, feel free to ask.

I forgot to include the itemNo from the SELECT statement of the CTE.
;WITH CTE AS (
	select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand) AS invDetails
		, ROW_NUMBER() OVER (PARTITION BY itemNo ORDER BY lastupdatedate) rnum
		, itemNo
	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

Open in new window

0
 

Author Comment

by:LovinSpoonful
ID: 36814768
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.  
0
 

Author Comment

by:LovinSpoonful
ID: 36814792
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...
0
 

Author Comment

by:LovinSpoonful
ID: 36814824
Hello Thomasian,  your update works perfectly and executes in 2 seconds.  Thanks for showing me how to do this.  What an awesome solution.
0
 

Author Closing Comment

by:LovinSpoonful
ID: 36814831
AWESOME!
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36815039
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
0
 
LVL 7

Expert Comment

by:luani
ID: 36815074
ISNULL will do the trick :

UPDATE b SET InvDetails =

(select isNULL(invDetails,'') from (select ' ' + Whse + '.' + rtrim(Locator) + '=' + convert(varchar,QtyOnhand) AS invDetails,
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) AS invDetails,
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) AS invDetails,
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.



0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36815143
@luani

the isnull / coalesce needs to cover the whole subquery not be within it

coalesce((subquery),'')
0
 
LVL 7

Expert Comment

by:luani
ID: 36815197
@Lowfatspread :

I think in his case both will work...but you are right it is better to cover the whole subquery


0
 

Author Comment

by:LovinSpoonful
ID: 36815218
For anyone using this solution, for the CTE solution,  don't forget to correct line 9 to say "3" not "2".
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question