GROUP BY syntax

I'm trying to group rows in ItemKey to produce a sum total of prices. Then
update ItemKeyprice. The select statement works, but I get the below error.
Where is my syntax wrong?
thanks
UPDATE ItemKeyPrice
SET RegPrice =
(SELECT ItemKey.KeyNumber, SUM(ItemKey.Price) AS PriceInd
FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
ItemKeyPrice.KeyNumber
GROUP BY ItemKey.KeyNumber)
error: Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.shank
(untested)
UPDATE ItemKeyPrice
SET RegPrice =
(SELECT SUM(ItemKey.Price) AS PriceInd
FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
ItemKeyPrice.KeyNumber
GROUP BY ItemKey.KeyNumber)
Note: Without seeing your table stucture + samle data I could only guess
,it is possible yo9u get an error like
"Subquery returned more than 1 value."
"shank" <shank@.tampabay.rr.com> wrote in message
news:eTrms$l$FHA.1288@.TK2MSFTNGP09.phx.gbl...
> I'm trying to group rows in ItemKey to produce a sum total of prices. Then
> update ItemKeyprice. The select statement works, but I get the below
> error. Where is my syntax wrong?
> thanks
> UPDATE ItemKeyPrice
> SET RegPrice =
> (SELECT ItemKey.KeyNumber, SUM(ItemKey.Price) AS PriceInd
> FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
> ItemKeyPrice.KeyNumber
> GROUP BY ItemKey.KeyNumber)
> error: Only one expression can be specified in the select list when the
> subquery is not introduced with EXISTS.
>|||shank (shank@.tampabay.rr.com) writes:
> I'm trying to group rows in ItemKey to produce a sum total of prices.
> Then update ItemKeyprice. The select statement works, but I get the
> below error. Where is my syntax wrong?
> thanks
> UPDATE ItemKeyPrice
> SET RegPrice =
> (SELECT ItemKey.KeyNumber, SUM(ItemKey.Price) AS PriceInd
> FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
> ItemKeyPrice.KeyNumber
> GROUP BY ItemKey.KeyNumber)
> error: Only one expression can be specified in the select list when the
> subquery is not introduced with EXISTS.
The immediate error is the inclusion of ItemKey.KeyNumber in the Select
list. You are assigning RegPrice value, but you are sending it two
values.
However, when you fix that error, you will get another error saying that
subquery returned more than one value.
Here are two ways of writing what I think you want to achieve:
UPDATE ItemKeyPrice
SET RegPrice = (SELECT SUM(ItemKey.Price)
FROM ItemKey
WHERE ItemKey.KeyNumber = ItemKeyPrice.KeyNumber)
UPDATE ItemKeyPrice
SET RegPrice = ik.totprice
FROM ItemKeyPrice ikp
JOIN (SELECT KeyNumber, totprice = SUM(ItemKey.Price)
FROM ItemKey
GROUP BY KeyNumber) AS ik ON ik.KeyNumber = ikp.KeyNumber
The first uses a correlated subquery, and this syntax is by the ANSI
standard and should run on any DBMS. The second uses a FROM clause in
the UPDATE statement. This syntax is particular to MS SQL Server and
Sybase, and thus less portable. This query also includes a derived
table (which is part of ANSI SQL) to compute the sums per key.
While the first syntax is portable, my preference is very strongly
for the second, as it is easier to write and understand, and usually
also gives better performance. Furthermore, assume that you would have
more columns to set, like an average price, min price etc. In this case,
you would have to have several correlated subqueries, but the FROM
solution is very extensible in this regard.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx