SQL Server : updating table with aggregate of itself

Question

I am using SQL Server 2012 and I am trying to update a table with its own aggregate data.

I have a query similar to this:

DECLARE @temp TABLE 
(
    RowNo int IDENTITY(1,1),
    Date date,
    ONDuration int,
    DateWiseONDuration int
)

INSERT INTO
    @temp(Date, ONDuration)
VALUES
    ('2014-05-01', 100);

INSERT INTO
    @temp(Date, ONDuration)
VALUES
    ('2014-05-01', 100);

INSERT INTO
    @temp(Date, ONDuration)
VALUES
    ('2014-05-02', 100);

UPDATE
    @temp
SET
    DateWiseONDuration = (
        SELECT
            SUM(ONDuration)
        FROM
            @temp t2
        WHERE
            @temp.Date = t2.Date
    );

SELECT * FROM @temp;

While executing it in SSMS, it says:-

Must declare the scalar variable "@temp".

If I write Date instead of @temp.Date, it sums up all ONDurations, hence defeats the purpose of WHERE clause. How can I achieve this in SQL Server 2012?

score 3 · Accepted Answer · answered May 31 '14 at 11:08

You could use a CTE (Common Table Expression) to achieve this:

;WITH AggregatedData AS
(
    SELECT Date, DurationSum = SUM(ONDuration)
    FROM @temp
    GROUP BY Date
)
UPDATE t
SET t.DateWiseONDuration = ad.DurationSum
FROM @temp t
INNER JOIN AggregatedData ad ON ad.Date = t.Date

This basically "pre-computes" the Date and SUM(ONDuration) in an "on-the-fly" view which you can then use to update the base table, using a proper UPDATE ... FROM .. INNER JOIN syntax

Or you can slightly modify your statement using proper table aliases like this:

UPDATE t1
SET t1.DateWiseONDuration = (SELECT SUM(ONDuration)
                             FROM @temp t2
                             WHERE t1.Date = t2.Date)
FROM @temp t1;

and that works, too.

The second option is pretty easy to understand. I will go with that one. — Prakhar Mishra, May 31 '14 at 11:20
The second format is less efficient than the first. See https://stackoverflow.com/a/2009948/235041 The first version can be written without a cte (although it's easier to read). The group by aspect of the first is more efficient than using a SUM() in the subquery with a where criteria — Sanjiv Jivan, Dec 15 '20 at 04:33

score 1 · Answer 2 · answered May 31 '14 at 12:25

1

The problem with your query is simply the definition of the table alias, as marc_s's last query fixes. I want to point out that you can do what you want quite easily with window functions:

WITH toupdate as (
      SELECT t.*, SUM(ONDuration) OVER (PARTITION BY date) as sumond
      FROM @temp t
     )
UPDATE toupdate
    SET DateWiseONDuration = sumond;

answered May 31 '14 at 12:25

Gordon Linoff

1,242,037
58
646
786

Never seen `OVER (PARTITION BY date)` before in any query. You skipped `WHERE` clause here. Is it not required? – Prakhar Mishra May 31 '14 at 12:33
@PrakharMishra . . . First you should learn about window functions. They are very useful and you may not even need to store the sum in the table. Second, I'm not sure what `where` clause you are referring to. Your original data doesn't suggest any filtering of the data. – Gordon Linoff May 31 '14 at 13:15
Please don't mind it. This is one pretty smart query. It got me puzzled, at first glance. You just used a single table (view) to do the job. Cool. – Prakhar Mishra May 31 '14 at 13:27

SQL Server : updating table with aggregate of itself

2 Answers2