Using SQL Server MERGE command with same source & target table

merge, sql-server

Solution

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.

Problem

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

Original source