Update count column from data in another table

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.

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

Update count column from data in another table

Related

Recent Posts