Better pull out the excuse-o-meter

I have no excuse, none whatsoever.

In my T-SQL Brush-Up presentation to the North Texas SQL Server User Group (NTSSUG) in September – and again in the T-SQL Brush-Up 24HOP session I gave that same day - I mentioned that you can only have one WHEN NOT MATCHED clause in a MERGE statement (as per SQL Server Books Online Merge (Transact-SQL) article).  Remember, if you will, that MERGE compares a source and a destination table, and allows you to take different actions based on whether rows match, do not match by the source table, or do not match by the target table.  Here’s the text of the BOL article, for your reference and my defense (emphasis mine):

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched> Specifies that a row is inserted into target_table for every row returned by <table_source> ON <merge_search_condition> that does not match a row in target_table, but does satisfy an additional search condition, if present. The values to insert are specified by the <merge_not_matched> clause. The MERGE statement can have only one WHEN NOT MATCHED clause.

To me, this said that you can take action on matched rows, plus on rows that don’t match EITHER by source or by destination, but not both*.  Actually, during the NTSSUG session I emphasized and lamented this fact, and had a reasonably big discussion on it, wishing that you could insert, update, AND delete all in the same statement (instead of just 2 out of the 3).  Wait for it….

(*This is WRONG WRONG WRONG.)

Some weeks later, I was rereading pieces of Itzik Ben-Gan‘s excellent book SQL  Server 2008 T-SQL Fundamentals, when I ran across this, on page 258 (emphasis mine, again):

…suppose that you want to add logic to our MERGE example to delete rows from the target when the target is not matched by a source row. All you need to do is add the WHEN NOT MATCHED BY SOURCE clause with a DELETE action, like so:
MERGE INTO Customers AS TGT

USING CustomerStage AS SRC

  ON TGT.custid = SRC.custid

WHEN MATCHED THEN

  UPDATE SET

  tgt.companyname = src.companyname ,

  tgt.phone = src.phone,

  tgt.address = src.address
WHEN NOT MATCHED THEN

  INSERT (custid, companyname, phone, address)

  VALUES (src.custid, src.companyname, src.phone, src.address)
WHEN NOT MATCHED BY SOURCE THEN

  DELETE;

Wait, what? Two WHEN NOT MATCHED statements?? I looked at the book’s errata page, to see if the error had been corrected. It hadn’t. Then I had a sneaking suspicion…could I have made a terrible mistake?  So, I entered in the code to test it.

*Ahem*  Kids, you CAN have a merge that does INSERT, UPDATE, and DELETE all in one operation….the book is right.  The BOL article is right, too.  That statement in BOL means that you can’t have more than one WHEN NOT MATCHED [BY TARGET] statement, which totally makes sense.  

I’m really glad my wish came true – the whole point of MERGE is to be able to perform all the operations you want (and there are only the three – insert, update, delete) on a table in a single statement.  But I’m quite unhappy that I let that huge mistake get by me…and especially so five years after the feature was introduced to SQL!

Lesson number 1: RTFM. Carefully.

Lesson number 2: Test your hypotheses.

Lesson number 3: You don’t really know anything - even basics! - until you’ve  taught it, and sometimes even then there’s lag time.

Happy days,

Jen McCown
http://www.MidnightDBA.com/Jen