Using SQL Server MERGE command with same source & target table

Question

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?

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);

Solution 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.

---

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

Solution 2

I guess you logic is wrong.

Your logic is:

if source is empty* then insert my row

*Source is empty when

SELECT '20120101' as [date] , 1 as sip , 2 as dip , 80 as port

returns 0 rows..

So in fact you're trying to merge target with empty source. It's logic error.

You should rather write something like this

IF EXISTS(SELECT * from iplog Where [date]='20120101' and sip=1 and dip=2 and port=80)
BEGIN
 UPDATE
   iplog 
 SET 
   t.size=t.size+1
 WHERE
   [date]='20120101' and sip=1 and dip=2 and port=80
END
ELSE
BEGIN
  INSERT INTO iplog VALUES ('20120101',1,2,80,1)
END

UPDATE: Imagine how MERGE works: You have empty source and not empty target.

MERGE can have two sorts of WHEN NOT MATCHED clauses

First,

[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]

That means for every row in source without a pair at target you can do INSERT into target. So if you have empty source there is no occasion to do any INSERT.

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ] 
        { VALUES ( values_list )
        | DEFAULT VALUES }
}

Second,

[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
    THEN <merge_matched> ] [ ...n ]

That means for every row in target without a pair at source you can do UPDATE or DELETE on target. No possibility to do INSERT.

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

To make MERGE works for you, you need not empy source. Your source is SELECT with WHERE clause, so it may become an empty source. So you should make it non empty with some sort of coditional logic, use some temporary table or table varialble or tricky JOIN or UNION.. But in that way your code may become unreadable for you. Better drop idea of MERGE in that case at all and do classic conditional UPDATE or INSERT.