Refresh a column with data in another table

Question

In my database, I have two tables: Elements (Id, ..., ToatlViews int) and ItemViews (id, ItemId, Timestamp)

In the ItemViews table, I save all the item’s views as they arrive on the site. From time to time I want to call a stored procedure to update the Items.ToatlViews field. I tried to make this SP with the cursor ... but the update expression is incorrect. Can you help me fix this? Can I do this without a 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 

Answer 1

You can use the direct UPDATE statement

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

You can test performance for various ways of doing this, if that matters.

Answer 2

Instead of the cursor, you can use update ... from :

 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 

Answer 3

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

But why do you want to keep this value when you can always get an invoice at runtime? You will have to run this update statement every time you touch the ItemViews table in any way, otherwise the account stored with the items will be incorrect.

Instead, you can create 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 the scan in your queries and know that the invoice is always updated (without paying a fine for scanning to count each item). The disadvantage of an indexed view is that you pay this cost gradually when there is an insert / update / delete in the ItemViews table.

Answer 4

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

I think this is a better solution than doing something manually in order to recount, since there is no way to forget about running 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 

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