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.
MS Sql Server LazyDBA home page