upsert with addition
问题描述:
如何在Microsoft SQL Server 2008中编写以下内容?upsert with addition
IF EXISTS(SELECT * FROM Table WHERE Something=1000)
UPDATE Table SET Qty = Qty + 1 WHERE Something=1000
ELSE
INSERT INTO Table(Something,Qty) VALUES(1000,1)
答
在SQL Server中使用新的merge
命令2008年会,据我可以收集为:
merge Table t
using (select 1000 as Something, 1 as Qty) s on t.Something = s.Something
when matched then
update set t.Qty = t.Qty + 1
when not matched by target then
insert values (s.Something, s.Qty);
这不是那么简单,因为合并是更高效的合并大集,而不仅仅是一个单一的记录。否则,您可以尝试更新记录,并在没有更新记录时插入一条记录:
update Table set Qty = Qty + 1 where Something = 1000
if (@@rowcount = 0) begin
insert into Table (Something, Qty) values (1000, 1)
end
答
IF EXISTS(SELECT * FROM Table WHERE Something=1000)
BEGIN
UPDATE Table SET Qty = Qty + 1 WHERE Something=1000
END
ELSE
BEGIN
INSERT INTO Table(Something,Qty) VALUES(1000,1)
END
我喜欢你的第二个解决方案。很紧。我甚至开始和结束。 – 2010-05-26 21:00:40