CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Prakhar Mishra
Prakhar Mishra

Reputation: 1814

SQL Server : updating table with aggregate of itself

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?

Upvotes: 0

Views: 1034

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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;

Upvotes: 1

marc_s
marc_s

Reputation: 755541

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.

Upvotes: 3

Related Questions