CODEWITHSUNDEEP
t-sqlcursorsql-updateaggregate
Bondolin
Bondolin

Reputation: 3121

Adding a SQL COUNT() result to a column value

I have two tables, Link and Hit. The Hit table contains information about when links from Link were hit. The Link table contains a column HitsCount with the number of times each link has been hit. We regularly aggregate Hit's data into aggregations of data for each hour of hits, and remove the aggregated data to save space. When we aggregate, we want to also update Link.HitsCount with the COUNT() of links for the current hour we are aggregating over. How is this done?

Here is my current attempt, using T-SQL:

DECLARE cc CURSOR LOCAL FOR
    SELECT COUNT(*) AS c, h.LinkId AS id
    FROM   Hit AS h
    WHERE  h.[Timestamp] >= @p_startTime AND h.[Timestamp] < @p_endHour
    GROUP BY h.LinkId

OPEN cc
FETCH cc

UPDATELink
SET Link.HitsCount = HitsCount + c
FROM Link
WHERE CURRENT OF cc

CLOSE cc
DEALLOCATE cc

However, I get the error "Invalid column name 'c'." Also, this approach does not JOIN ON h.LinkId and Link.LinkId like it should. I've thought about dropping and populating a scratch table with the LinkId and COUNT(), but I'd rather avoid this. On the other hand, I'd also rather avoid a CURSOR.

Many thanks.
Nathan

Upvotes: 1

Views: 575

Answers (2)

T I
T I

Reputation: 9933

there is no need for a cursor, you should be doing something like

UPDATE Link
SET HitsCount = HitsCount + (SELECT COUNT(1) FROM Hit 
                             WHERE Hit.LinkId = Link.Id
                             AND Hit.[Timestamp] >= @p_startTime 
                             AND Hit.[Timestamp] < @p_endHour)

or

;WITH Hits AS
(
    SELECT COUNT(1) AS freq, h.LinkId
    FROM   Hit AS h
    WHERE  h.[Timestamp] >= @p_startTime AND h.[Timestamp] < @p_endHour
    GROUP BY h.LinkId
)

UPDATE l
SET l.HitCount = l.HitCount + h.freq
FROM Link l
JOIN Hits h ON h.LinkId = l.Id

Upvotes: 3

Barmar
Barmar

Reputation: 780798

Use a JOIN with a subquery:

UPDATE Link l
JOIN (SELECT LinkId, COUNT(*) HitsCount
      FROM Hit
      WHERE h.[Timestamp] >= @p_startTime AND h.[Timestamp] < @p_endHour
      GROUP BY LinkID) h
USING (LinkId)
SET l.HitsCount = l.HitsCount + h.HitsCount

Upvotes: 1

Related Questions