Update FIELD from Select Aggregated Statement on second table

Question

Update FIELD from Select Aggregated Statement on second table

SteveEClarke

SSCertifiable

Points: 5161
More actions
April 10, 2014 at 5:02 am

#292253

Hi all,
I have a Warehouse Layout table which contains the warehouse and locations, which I would like to populate with stock quantities from another table ( aggregated )
The code I have is as below -
update
[dbo].[SLGWarehouseLayout]
set Qty =
(
Select
InvMultBin.Warehouse, InvMultBin.Bin,
SubString( InvMultBin.Bin, 1, 1 ) as xx,
SubString( InvMultBin.Bin, 3, 2 ) as yy,
SubString( InvMultBin.Bin, 2, 1 ) as zz,
Sum( QtyOnHand1 ) as TotalStock
from InvMultBin
join [SLGWarehouseLayout]
on
SubString( InvMultBin.Bin, 1, 1 ) = SubString( [dbo].[SLGWarehouseLayout].Rack,1,1 ) and
SubString( InvMultBin.Bin, 3, 2 ) = SubString( [dbo].[SLGWarehouseLayout].Bay ,1,2) and
SubString( InvMultBin.Bin, 2, 1 ) = SubString( [dbo].[SLGWarehouseLayout].Height,1,1 )
where InvMultBin.Warehouse in ( 'W1','SC') and QtyOnHand1 <> 0
group by InvMultBin.Warehouse, InvMultBin.Bin having Sum( QtyOnHand1 ) > 0
)
But I get the following error message ;
Msg 116, Level 16, State 1, Line 2
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
any help would be appreciated -
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

BI_Baracus SSC-Addicted Points: 495 More actions · Answer 1

Hi Steve,

it looks to me as though you are trying to update the QTY column using a sub query that brings back more than one column. Change the select to assign only one value to the QTY and you should be fine.

is this actually a syntax error and you were trying to use a derived table?

thanks

J

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 2

Ahh right - sorry - yes I was playing with just the SELECT part of the query - which returns correctly and then I forgot to uncomment the bin, Rack

But when I run it - I appear to get the incorrect totals populate into the holding table, rather than the ones returned from the inner SELECT statement.

Regards

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

The inner select is uncorrelated - it will update every row of the target.

Which table contains column QtyOnHand1?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Here's a guess:

-- ALWAYS check first using a SELECT

SELECT

l.rack, l.bay, l.height, l.qty, x.TotalStock, x.Warehouse, x.Bin

FROM [SLGWarehouseLayout] l

CROSS APPLY (

SELECT SUM(i.QtyOnHand1) AS TotalStock, i.Warehouse, i.Bin

FROM InvMultBin i

WHERE i.Warehouse IN ('W1','SC')

AND i.QtyOnHand1 <> 0

AND LEFT(i.Bin, 1) = LEFT(l.Rack, 1)

AND SUBSTRING(i.Bin, 3, 2) = LEFT(l.Bay, 2)

AND SUBSTRING(i.Bin, 2, 1) = LEFT(l.Height, 1)

GROUP BY i.Warehouse, i.Bin

HAVING SUM(i.QtyOnHand1) > 0

) x

-- If the SELECT works, convert it to the UPDATE equivalent

UPDATE l SET

qty = x.TotalStock

FROM [SLGWarehouseLayout] l

CROSS APPLY (

SELECT SUM(i.QtyOnHand1) AS TotalStock

FROM InvMultBin i

WHERE i.Warehouse IN ('W1','SC')

AND i.QtyOnHand1 <> 0

AND LEFT(i.Bin, 1) = LEFT(l.Rack, 1)

AND SUBSTRING(i.Bin, 3, 2) = LEFT(l.Bay, 2)

AND SUBSTRING(i.Bin, 2, 1) = LEFT(l.Height, 1)

GROUP BY i.Warehouse, i.Bin

HAVING SUM(i.QtyOnHand1) > 0

) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 5

Totally brilliant - thank you.

In the meantime I have used the PIVOT function to create a View - dropped into Excel and then conditional formatting to show the current warehouse stock population.

Thank you for the help

Regards

Steve

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

You're welcome. Thanks for the feedback. If you have any questions about the solution, post back.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden