在SQL中安全地更新计数(字段) [英] Update a count (field) safely in SQL
问题描述
我想实现SO像用户帖子上的标签。我有一个表tag_data与列tagId,标题,计数。我有一个单独的表,链接一个帖子和它可能使用的许多标签之间的关系。
遇到问题,如何获得当前计数,增加或减少它由一个并存储它SAFELY。所以没有其他连接/线程会更新它之间的时间我做选择和更新?
我假设你也想要新的如果你的数据库支持输出语句在UPDATE(例如SQL Server 2K5或2K8),这是一个没有脑子,只是更新设置计数=计数+ 1
< ):
UPDATE表
SET count = count + 1
OUTPUT inserted.count
WHERE id = @ id;
否则:
code> begin transaction
update table
set counter = counter + 1
其中id = @ id;
select counter
from table
其中id = @ id;
commit;
I want to implement SO like tags on userpost. I have a table called tag_data with columns tagId, title, count. I have a separate table that links the relationship between a post and the many tags it may use.
Heres the problem, how do i get the current count, increase or decrease it by one and store it SAFELY. So no other connection/thread will update it between the time i do select and update?
I assume you also want the new count, other wise this is a no brainer, just update set count=count+1.
If your db support output clause on UPDATE (eg. SQL Server 2K5 or 2K8):
UPDATE table
SET count = count + 1
OUTPUT inserted.count
WHERE id=@id;
otherwise:
begin transaction
update table
set counter=counter+1
where id=@id;
select counter
from table
where id=@id;
commit;
这篇关于在SQL中安全地更新计数(字段)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!