Update count column from data in another table

Question

In my DB I have two tables Items(Id, ..., ToatlViews int) and ItemViews (id, ItemId, Timestamp)

In ItemViews table I store all views of an item as they come to the site. From time to time I want to call a stored procedure to update Items.ToatlViews field. I tried to do this SP using a cursor ... but the update statement is wrong. Can you help me to correct it? Can I do this without cursor?

CREATE PROCEDURE UpdateItemsViews
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @currentItemId int
    DECLARE @currentItemCursor CURSOR
    SET @currentItemCursor = CURSOR FOR SELECT Id FROM dbo.Items

    OPEN @currentItemCursor
    FETCH NEXT FROM @currentItemCursor INTO @currentItemId
    WHILE @@FETCH_STATUS = 0
    BEGIN
        Update dbo.Items set TotalViews = count(*) 
              from dbo.ItemViews where ItemId=@currentItemId
        FETCH NEXT FROM @currentItemCursor INTO @currentItemId
    END   
END
GO

Cardiff

I recommend you make every effort to not use cursors when writing SQL, as there will always be a set based way to write what you want against the database. Of course there will always be an exception to this rule. – Kings

Answer 1

You can use a direct UPDATE statement

update Items set TotalViews = 
     (select COUNT(id) from ItemViews where ItemViews.ItemId = Items.Id)

You might want to test performance for the various ways to do this, if that's important.

Gasiform

+1 for elegance. Talking of performance; I remember reading somewhere that count(1) instead of count(id) is better (not that you'd probably notice it). Since the id field does need to be selected out as part of the query... – Kings

@mouters that is a misconception. COUNT(1) is only better than COUNT(id) if we're talking about accuracy, and it is only more accurate (or different in any way) if id is nullable. If you see this stated somewhere (other than your memory), please point it out because it should be corrected or clarified. – Auten

Fair comment - I think I'll have a hard time finding the article again. – Kings

Problem with this, is that it will set TotalViews to 0 for everything that doesnt match the select – Hate

@Hate you mean where there's no match between Items and ItemViews? Whether in that case TotalViews should be 0 or null is a business rule. In this case OP wants 0: for null it would use a different statement. – Gasiform

@Gasiform yes, i am just pointing that out to anyone else to be aware of. In the case where you dont want all non-matching records being set to 0 or null you would need to use a CTE as posted by below – Hate

Answer 2

You could use update ... from instead of a cursor:

update  i
set     TotalViews = iv.cnt
from    dbo.Item i
join    (
        select  ItemId
        ,       count(*) as cnt
        from    dbo.ItemViews
        group by
                ItemId
        ) iv
on      i.Id = iv.ItemId

Pe

Answer 3

;WITH x AS 
(
  SELECT ItemID, c = COUNT(*) 
  FROM dbo.ItemViews
  GROUP BY ItemID
)
UPDATE i
SET TotalViews = x.c
FROM dbo.Items AS i
INNER JOIN x
ON x.ItemID = i.ItemID;

But why do you want to store this value, when you can always get the count at runtime? You're going to have to run this update statement every time you touch the ItemViews table in any way, otherwise the count stored with Items is going to be incorrect.

What you may consider doing instead is setting up an indexed view:

CREATE VIEW dbo.ItemViewCount
WITH SCHEMABINDING
AS
    SELECT ItemID, ItemCount = COUNT_BIG(*)
      FROM dbo.ItemViews
      GROUP BY ItemID;
GO
CREATE UNIQUE CLUSTERED INDEX x ON dbo.ItemViewCount(ItemID);

Now you can join to the view in your queries and know that the count is always up to date (without paying the penalty of scanning for the count of each item). The downside to the indexed view is that you pay that cost incrementally when there are inserts/updates/deletes to the ItemViews table.

Auten

Answer 4

I found this question / answer a year after it was written and answered. the answer was okay, but I was after something a bit more automatic. I ended up writing a trigger to automatically recalculate the column when a relevant row in the other table was inserted, deleted or updated.

I think it's a better solution than running something manually to do the recalculation as there isn't any possibility of someone forgetting to run the code:

CREATE TRIGGER [dbo].[TriggerItemTotalViews] 
   ON  [dbo].[ItemViews]
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
SET NOCOUNT ON;

UPDATE [Items] 
SET [TotalViews] = 
    (
    SELECT COUNT(id) 
    FROM [ItemViews] 
    WHERE [ItemViews].[ItemId] = [Items].[ItemId]
    )
WHERE [Items].[ItemId] IN
    (
    SELECT [ItemId] FROM [INSERTED] 
    UNION 
    SELECT [ItemId] FROM [DELETED]
    )
END

Goodygoody

Answer 5

Same but different:

declare @productId int = 24;
declare @classificationTypeId int = 86;

update s
set CounterByProductAndClassificationType = row_num
from Samples s
join
(
    select row_number() over (order by (select Id)) row_num, Id
    from Samples
    where 
        ProductId = @productId and
        ClassificationTypeId = @classificationTypeId
) s_row on s.Id = s_row.Id

Pithy

Answer 6

For who need to include zero count too

UPDATE Items as i,
    (SELECT 
        i.Id as Id, COUNT(iv.ItemId) AS c
    FROM
        Items AS i
    LEFT JOIN ItemViews AS iv ON i.Id = iv.ItemId
    GROUP BY i.Id) AS ic 
SET 
    i.TotalViews = ic.c
WHERE
    i.Id = ic.Id

Vivien

Answer 7

update Table1 Set Total=(Select Count(Id) from Table2 where Id=123) where Id=123

Cerebrospinal