从另一个表中的数据更新计数列 [英] 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屋!