MERGE Statement Corrected, or, How to Eat Crow in Public

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

5 thoughts on “MERGE Statement Corrected, or, How to Eat Crow in Public

  1. Rob Volk

    > 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!

    Ummmm…MERGE was added to SQL Server 2008, which was only 2 years ago.

    Granted, it existed in other products before then…

  2. Jorge Segarra

    Very cool and kudos for admitting fault admirably. To help others avoid pitfall apparently they’ve added a piece to BOL where you can contribute examples (Community Content) down at the bottom. http://msdn.microsoft.com/en-us/library/bb510625.aspx

    I suggest you write up a similar example and submit it there or ask Itzik if you can copy/paste his example there and credit him so as to clarify this for any future knowledge seekers.

  3. Pingback: Tweets that mention SQL Awesomesauce » Blog Archive » MERGE Statement Corrected, or, How to Eat Crow in Public -- Topsy.com

  4. Pingback: SQL Awesomesauce » Blog Archive » Learn T-SQL in 5 Minutes

Comments are closed.