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

How can the target table be the source when you're aiming for a "when not matched"? What would that even mean? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented May 29, 2012 at 7:45

Damien_The_Unbeliever · Accepted Answer · 2012-05-29 07:48:49Z

20

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.

answered May 29, 2012 at 7:48

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

meraydin Over a year ago

I'm planning to use this in a procedure like: InsertOrUpdate(DateTime.Date, sourceIP, DestIP, Port, PacketSize). So, if the respetive record exists it will increment the packet size, otherwise insert a new record with the values.

huhu78 · Accepted Answer · 2017-05-30 11:17:32Z

6

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.

edited May 30, 2017 at 11:17

answered May 29, 2012 at 8:23

huhu78

3892 silver badges13 bronze badges

3 Comments

meraydin Over a year ago

This is what I want to do but with MERGE command. Is this possible?

huhu78 Over a year ago

Of course you can do it with MERGE but at first you need not empty source like tabular variable, temporary table, common table expression - but still to fill your source table you need some sort of conditional logic. MERGE is very useful but it does not fit everywhere.

sharad shrestha Over a year ago

SELECT '20120101' as [date] , 1 as sip , 2 as dip , 80 as port will return one row..

Collectives™ on Stack Overflow

Using SQL Server MERGE command with same source & target table

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related