Home » 24HOP »24HOP »Beginner »Intermediate »News-ish »NTSSUG »sql server »SQLPASS »sqlserverpedia-syndication »SSC »TSQL » Currently Reading:

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

Currently there are "5 comments" on this Article:

  1. Rob Volk says:

    > 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. Jen McCown says:

    Oh, great, why don’t you just give me a papercut and pour lemon juice on it!??!

    I mean, thanks for the correction… :)

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

  4. [...] This post was mentioned on Twitter by Jen & Sean McCown, Jen & Sean McCown. Jen & Sean McCown said: Blog: MERGE Statement Corrected, or, How to Eat Crow in Public. "I have no excuse, none whatsoever." http://bit.ly/a34Mwm #24hop #sqlpass [...]

  5. [...] MERGE Statement Corrected, or, How to Eat Crow in Public [...]

Comment on this Article:







Minion Reindex by MidnightDBA is here!

 

Excellent Index Maintenance

Download Minion Reindex, log feature requests, read documentation, and sign up for the newsletter at MidnightSQL.com/Minion!




Monday, Oct 27 12:00PM CDT: Attend the Minion Reindex Intro Webinar.

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/