将SQL Server MERGE命令与相同的源&目标表 [英] Using SQL Server MERGE command with same source & target table

问题描述

我正在尝试使用MERGE命令插入或更新单个表，但是我总是收到受影响的0行"的信息.我的目标很简单:如果存在更新，否则插入.我在做什么错了?

I'm trying to insert or update a single table using the MERGE command however I always get a "0 rows affected". My goal is simple: if exists update, otherwise insert. What am I doing wrong?

注意:表的主键是组合键= [日期] + sip + dip + port 其中date为datetime，所有其他字段均为int

Note: the table's primary key is a combo key = [date]+sip+dip+port where date is datetime and all other fields are int

merge iplog as t
using (SELECT * from iplog Where [date]='20120101' and sip=1 and dip=2 and port=80) as s
on t.[date]=s.[date] and t.sip=s.sip and t.dip=s.dip and t.port=s.port
when matched then
    update set t.size=t.size+s.size
when not matched then
    insert values ('20120101',1,2,80,1);

推荐答案

我认为如果date，sip，dip和port，但尚不清楚在UPDATE条件下需要什么尺寸.我选择了1:

I think you're wanting to insert a new value if there isn't currently one matching by date, sip, dip and port, but it's unclear what size you want in the UPDATE condition. I've picked 1:

create table iplog (
    [date] date not null,
    sip int not null,
    dip int not null,
    port int not null,
    size int not null
)
GO
merge iplog as t
using (SELECT '20120101' as [date] , 1 as sip , 2 as dip , 80 as port) as s
on t.[date]=s.[date] and t.sip=s.sip and t.dip=s.dip and t.port=s.port
when matched then
    update set t.size=t.size+1 --What should this be?
when not matched then
    insert values ('20120101',1,2,80,1);

select * from iplog

您会注意到源现在根本没有引用目标表.

You'll note that the source doesn't reference the target table at all now.

旁注-我建议避免将诸如date之类的SQL关键字用作列名.

Side note - I'd recommend avoiding SQL keywords such as date as column names.

这篇关于将SQL Server MERGE命令与相同的源&目标表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！