从另一个表中的数据更新计数列 [英] Update count column from data in another table

问题描述

在我的数据库中，我有两个表 Items(Id, ..., ToatlViews int) 和 ItemViews (id, ItemId, Timestamp)

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

在 ItemViews 表中，我存储项目到达站点时的所有视图.有时我想调用一个存储过程来更新 Items.ToatlViews 字段.我尝试使用游标来执行此 SP……但更新语句是错误的.你能帮我改正吗?我可以在没有光标的情况下执行此操作吗?

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

推荐答案

可以直接使用 UPDATE 语句

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.

这篇关于从另一个表中的数据更新计数列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！