Theoretical question: Why can't you use an aggregate in a SET clause of an
UPDATE Statement?
Is it because the set clause could possibly update the field the tables are
joined on, affecting the results returned in the join???
__________________________________________
Anthony Hand
DBA | Systems Integration
Aon Risk Services Australia Ltd
Ph: +61 2 9253 7516
Fax: +61 2 92537952
Mob: +61 415 877 988
"Greg"
<[Email Address Removed] To: "LazyDBA.com Discussion" <[Email Address Removed]
fargo.com> cc:
bcc:
29/05/2003 Subject: RE: UPDATE and Aggregates
04:00 AM
Thanks Tom,
The actual tables involved are quite large and I'll only be updating a
small portion of the records in T based on a small portion of the records
in S. The way it looks, taking out the JOIN will update _all_ records in T
regardless of what I'm selecting from S. So I think I'll stick with thte
subquery _and_ join option.
Theoretical question: Why can't you use an aggregate in a SET clause of an
UPDATE Statement?
-Greg
-----Original Message-----
From: thomas.[Email Address Removed] [mailto:thomas.[Email Address Removed] Wednesday, May 28, 2003 10:03 AM
To: Greg
Subject: Re: UPDATE and Aggregates
Greg,
Depending on the sizes of the tables, and the number of rows you expect to
update, you may be able to get away with:
UPDATE T SET T.Qty = T.Qty - (SELECT SUM(S.Qty) FROM S WHERE S.KeyF1 =
T.KeyF1
AND S.KeyF2 IN (1, 2, 3))
-- FROM T INNER JOIN S ON T.KeyF1 = S.KeyF1
-- WHERE S.KeyF2 IN (1, 2, 3)
Having the join to S limits the number of rows that you read from T but if
you are doing a tablescan anyway, then there may be little reason to have
the from clause. Check it out for performance and if it is the same both
ways, I would keep it as it is in case the profile of the data changes.
my 2 cents
. . .Tom
On 05/28/2003 10:27:57 AM Greg wrote:
> Hi List,
>
> Please consider the following:
>
> CREATE TABLE T
> (KeyF1 INT PRIMARY KEY,
> Qty Int)
>
> CREATE TABLE S
> (KeyF2 INT PRIMARY KEY,
> KeyF1 INT,
> Qty Int)
>
> INSERT INTO T (KeyF1, Qty) VALUES (1, 1000)
> INSERT INTO T (KeyF1, Qty) VALUES (2, 1000)
> INSERT INTO T (KeyF1, Qty) VALUES (3, 1000)
> INSERT INTO S (KeyF2, KeyF1, Qty) VALUES (1, 1, 50)
> INSERT INTO S (KeyF2, KeyF1, Qty) VALUES (2, 2, 50)
> INSERT INTO S (KeyF2, KeyF1, Qty) VALUES (3, 1, 50)
> INSERT INTO S (KeyF2, KeyF1, Qty) VALUES (4, 2, 50)
> INSERT INTO S (KeyF2, KeyF1, Qty) VALUES (5, 3, 50)
>
> I want to update table T with aggregate values from table S Like so...
>
> UPDATE T SET T.Qty = T.Qty - SUM(S.Qty)
> FROM T INNER JOIN S ON T.KeyF1 = S.KeyF1
> WHERE S.KeyF2 IN (1, 2, 3)
> GROUP BY S.KeyF1
>
> The result I'm Looking for:
>
> SELECT * FROM T
>
> KeyF1 Qty
> ----- ------
> 1 900
> 2 950
> 3 1000
>
> This doesn't work of course, "An aggregate may not appear in the set list
of an
> UPDATE statement."
>
> So this is how I got it to work:
>
> UPDATE T SET T.Qty = T.Qty - (SELECT SUM(S.Qty) FROM S WHERE S.KeyF1 =
T.KeyF1
> AND S.KeyF2 IN (1, 2, 3))
> FROM T INNER JOIN S ON T.KeyF1 = S.KeyF1
> WHERE S.KeyF2 IN (1, 2, 3)
>
> My question is: Is there a better way to do this? Using this method I
have to
> specify the selection criteria twice and the join criteria twice. I
don't want
> to use a temp table or a cursor, Any Ideas?
>
> -Greg
-----------------------------------------------------------------
Visit our Internet site at http://www.reuters.com
Get closer to the financial markets with Reuters Messaging - for more
information and to register, visit http://www.reuters.com/messaging
Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page