find a location for property in a new city

Friday 17 May 2013

SQL MERGE that changes the ON constraint as it runs

What happens when your MERGE statement INSERTs a row that now satisfies the ON constraint. If a new row comes along that satisfies the ON constraint will it fall into WHEN MATCHED or WHEN NOT MATCHED?

Let's use an example piece of code to decide what happens. Here I have two table variables created and seeded with some random data:

DECLARE @t1 Table (id int, name varchar(12))
INSERT INTO @t1 VALUES(1, 'hi')

DECLARE @t2 Table (id int, name varchar(12))
INSERT INTO @t2 VALUES(1, 'bye')
INSERT INTO @t2 VALUES(3, 'colin')
INSERT INTO @t2 VALUES(3, 'sarah')

Now I will attempt to MERGE @t2 into @t1. If you have not come across MERGE before it is basically a more efficient way of saying UPDATE if it exists or INSERT if it is new.

MERGE @t1 AS t1
 USING(SELECT * FROM @t2) AS t2
 ON t2.id = t1.id
WHEN MATCHED THEN
 UPDATE SET t1.name = t2.name
WHEN NOT MATCHED THEN
 INSERT(id, name)
 VALUES(t2.id, t2.name);

So, what we are saying is:

  • USING this source of data (SELECT * FROM @t2)
  • ON this decider (matching the ID's to judge if this row already exists)
  • WHEN the ON clause is MATCHED update this row with new values
  • WHEN NOT MATCHED we should INSERT the new row

But, given the values in @t2, what will happen?

  1. The 1st row will match on ID 1 and so will do an UPDATE
  2. The 2nd row doesn't have a match for an ID of 3 so will INSERT
  3. The 3rd row didn't have a match for an ID of 3 before but since the last INSERT it now does. So INSERT or UPDATE?

A SELECT * FROM @t1 will show you that it has INSERTed twice...

idname
1bye
3colin
3sarah

So watch out for this. The constraint is decided about the source and destination once at the beginning and not again so you should be sure that the source table being MERGED is complete in itself. You can do with using a GROUP BY or DISTINCT on the USING table. Whichever is most appropriate for your scenario.

Follow britishdev on Twitter

110 comments:

  1. Wonderful blog!Friv. This is very informative site. I am totally pleased by your excellent work. Many thanks for sharing.Friv 10000

    ReplyDelete
  2. Estaba buscando esta información, finalmente la encontré, gracias mucho.
    friv juegos

    ReplyDelete
  3. Latest Govt Job Notification 2016

    IBPS Specialist Officer Recruitment Notification 2015-16

    It’s fantastic that you are getting thoughts from this piece of writing as well as from our argument made here..............

    ReplyDelete
  4. Thesis writing has become an essential part of completing the degree day.

    Cheap Dissertation Writing Service

    ReplyDelete
  5. To flee war, to protect the lives of themselves and their families.
    Mario Games | Friv | Doraemon Games | Kizi | Pou Games | Dora games

    ReplyDelete
  6. Kizi1000.in is a great place to play the best free online Kizi games, juegos Kizi, jogos Kizi, jeux de Kizi from the top game developers all over the world. Play free online at Kizi1000.in.

    ReplyDelete
  7. Thank so much and i like it! Kizi 3

    ReplyDelete
  8. Friv 2019 is all new Friv games online free for kids. We have include games from Friv, Kizi, Y8, Yepi... Visit and play now, free for fun and new games!Toongames.in update new-best free Cartoon Games online from internet. Toon Games is a free online cartoon flash games site where you can find games from all your favorite cartoons such as Ben10, Dora, Tom and Jerry, Spongebob and many more.

    ReplyDelete
  9. I love all the postsFriv 4 School,Kizi 8, I really enjoyed,would like more information about thisKizi 9K7x, because it is very niceFriv 8, Thanks for sharingFriv4School

    ReplyDelete
  10. Thank so muc! Please visit adress for kids and you feed love your like
    Y8 | Juegos de Twizl | Friv

    ReplyDelete
  11. When you alone, you can visit page game and Play, Comment & Share on your Facebook, G+, Twitter...! Thank so much. Friv | Doraemon | Kizi

    ReplyDelete
  12. Very nice. Posts shared useful information and meaningful life, I'm glad to be reading this article and hope to soon learn the next article. thank you
    العاب
    new تلبيس نهاية الاسبوع
    تلبس ملكات 2017الطبيعة
    تلبيس حفلة

    ReplyDelete
  13. Please visit top best include online funny for kid. Top Flsh 3D, 4D, 5D Juegos 2017.
    Kizi 1000 | Kizi | Y8 Dress Up | Friv yoob | Twizl

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. Thank so much.Busy life circumstances than the current world history. Must try to earn money to support themselves, their families and pay for living.
    Y8
    Y8 Dress Up
    Y8 2 Players
    Juegos De Twizl
    Twizl

    ReplyDelete
  16. We are so fortunate to have an innovator like you on our team. Yepi
    Friv Games

    ReplyDelete
  17. Story top include online fun kid.
    Y8 | Y8 Dress Up | Y8 2 Players

    ReplyDelete
  18. As I website possessor I believe the content material here is rattling fantastic , appreciate it for your efforts. You should keep it up forever! Best of luck.
    Tangki Fiberglass
    Jual Septic Tank
    Jual Tangki Kimia
    Jual Talang Fiber
    Jual Rotameter
    Tangki Panel
    jual mesin ro

    ReplyDelete
  19. Kizi 100 games - This is the best solution for having fun. Now play and feel! friv 4 school games is a great store for children's games. Where you can find the best friv games for school and free friv games for kids.

    ReplyDelete
  20. Playing games with beautiful people in my Web page!
    Friv Games
    Y2
    Friv 200

    ReplyDelete
  21. I liked it so much and very interesting, too! Thanks for sharing the experience.
    Wonderful post, really great tips and advice.
    friv 3
    friv 4
    kizi 2
    zoxy games

    ReplyDelete
  22. The cost of education is rising. While primary and secondary education options easily accessible to most, tertiary education is something that is left only to those with resources to spare. This sad fact is underscored by the many who are struggling in jobs that do not provide much in terms of career advancement even when they are skilled and competent enough for better jobs.http://www.how-todo.xyz/

    ReplyDelete
  23. Come to Bob the Robber 2 | kizi.com, you will have the opportunity to discover thousands of the hottest games in the world with many genres such as action games, puzzle games, strategy games, fashion games or racing games. Click now!

    ReplyDelete
  24. You need to kill time, you need entertainment. Refer to our website. hope you get the most comfort.
    Thanks you for sharing!
    please see: How to draw a whale

    ReplyDelete
  25. For Latest updates of Bollywood, Hollywood and Tollywood Check this blog
    Newsvx
    Shahrukh khan upcoming movies
    We are the best event organisers in delhi ncr
    Event organisers in delhi ncr
    social media marketing company in delhi
    Most of the people want graphic design in india to grow there product and brand
    Graphic design in delhi
    Best event management company in delhi ncr
    Webslogin is the best web designing company in india
    best web designing company in India
    Best wedding planning company in delhi ncr
    We are the best web designers in delhi ncr
    web designers in delhi

    ReplyDelete
  26. Motifz Designer Lawn. 1, 2 & 3 Piece Unstitched Premium Embroidered Lawn 2019, Premium Lawn, Lawn 2019, Best lawn collection, Motifz, Best Lawn in Pakistan, Shipping worldwide. Stitching option available.

    ReplyDelete
  27. very helpful post thanks for sharing this keep posting.
    you'll love to see my site micro blaster
    as well.

    ReplyDelete
  28. There are even alternatives to set up VPN and neighborhood from the supplier as a feature of their interchanges solution process. managed it support services

    ReplyDelete
  29. Looking For a Special Certificate?

    Often clients need something they can't spot on our site. There are thousands of different fake certificates out there, there is a good chance for us that we haven't gotten around to add the certificate you want most. If so, take a moment to contact us and explain your needs. If we are able to assist you, we will be glad to do so.

    For more details you can visit our website: https://buyalldocument.com/ OR WhatsApp: +1(443) 351-8162 OR Email us at buyrealfake.documents@yandex.com

    ReplyDelete
  30. Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! It’s always nice when you can not only be informed, but also entertained! click here

    ReplyDelete
  31. Buy online wholesale printed custom eyelash boxes packaging at seashore packaging in affordable price at your doorstep. Get free custom quote now!!

    ReplyDelete
  32. Buy online wholesale printed custom perfume boxes and packaging at easy prices and shipping worldwide, with 24/7 customer support. Get a free custom quote now!!

    ReplyDelete
  33. Buy online wholesale custom custom candle boxes and packaging at seashore packaging at the easy price Free shipping worldwide with 24/7 customer support.

    ReplyDelete
  34. Buy online wholesale candle boxes and packaging at seashore packaging at the easy price Free shipping worldwide with 24/7 customer support.

    ReplyDelete
  35. Blackbird packaging provide you with any size, shape design, lipstick boxes sooner than any other packaging competitor in the market

    ReplyDelete
  36. Got some great knowledge from this post about SQL server. I really like your work. Thanks for sharing it. small business support services.

    ReplyDelete
  37. Give you exactly data entry and copy paste from website to Excel sheet on your format.

    Virtual assistant

    ReplyDelete
  38. Blackbird packaging provide you with any size, shape design, cbd bath bombs wholesale sooner than any other packaging competitor in the market

    ReplyDelete
  39. Packaging town is a one stop solution for the short-run custom apparel boxes with logo needs and supplies. At affordable prices. Request a free quote now.

    ReplyDelete

  40. Thank you for some other informative website. The place else may just I get that kind of information written in such a perfect method? I have a venture that I am simply now running on, and I’ve been at the glance out for such info.Baby Wipes Online UAE


    ReplyDelete
  41. business card boxes wholesale online from packaging town at best prices. get free shipping.

    ReplyDelete
  42. Shop Men's Zipper Hoodies in a wide range of colors and styles at Wild Goat. Winter Zipper Hoodies Collection in long and short hoodies in variety of colors is now available at Wild Goat. View our wide collection of men zipper hoodies online and stock up your wardrobe now. Prices of our zipper hoodies are too low to miss.

    ReplyDelete
  43. Shop Men's Zipper Hoodies in a wide range of colors and styles at Wild Goat. Winter Zipper Hoodies Collection in long and short hoodies in variety of colors is now available at Wild Goat. View our wide collection of men zipper hoodies online and stock up your wardrobe now. Prices of our zipper hoodies are too low to miss.

    ReplyDelete
  44. Packaging town is a one stop solution for the short-run tuck top boxes needs and supplies. At affordable prices. Request a free quote now.

    ReplyDelete
  45. Shop for the leading trends in Men's Jogger and Sweat Pants. Buy the best quality Men Jogger Pants online from Wild Goat. Shop for finest Men's Jogger Pants, Sweat Pants and Trousers at affordable rates. Style your Gym Look in Wild Goat's Jogger and Sweat pants the way you want. Shipping available all across Pakistan.

    ReplyDelete
  46. I am very happy after visiting your website with very useful information. I am from one of the digital marketing agency... To visit my agency click on link
    Visitdigitalguru

    ReplyDelete
  47. Choose body Massagers from a range of branded categories at Al-Fatah. Shop for body and back massagers, electric massagers online in Pakistan from all imported and local brands at Al-Fatah. We have extensive range of brands of body and back massagers and electric hand massagers at Al-Fatah.

    ReplyDelete
  48. very nice article the way you explain the smart watch is awesome

    ReplyDelete
  49. Blackbird packaging company can provide you with any size, shape design, and Custom soap boxes sooner than any other packaging competitor in the market

    ReplyDelete
  50. buy exercise cycle online from Al-Fatah at best prices. Get free shipping

    ReplyDelete
  51. buy polo shirts from wild goat clothing at best prices. Get free shipping

    ReplyDelete
  52. Buy pret collection 2021 online from Ilaha Cloothing at best Prices. Get free shipping

    ReplyDelete
  53. Buy t-shirts for men online from Wild Goat Clothing. Get free shipping

    ReplyDelete
  54. buy polo shirts online online from Wild Goat Clothing at best prices. Get free shipping

    ReplyDelete
  55. Blackbird packaging company can provide you with any size, shape design, and cookie packaging boxes sooner than any other packaging competitor in the market

    ReplyDelete
  56. buy bath bomb boxes online from blackbird packaging at best prices. Get free shipping

    ReplyDelete
  57. buy bemberg chiffon collection online from saffana.pk. Get free shipping

    ReplyDelete
  58. buy inner wear for girls online from dshred at easy prices. Get free shipping

    ReplyDelete
  59. buy inner wear for girls online from Dshred at easy prices. get free shipping

    ReplyDelete
  60. buy baby care products online in pakistan at best prices delivered at your doorstep at best prices. get free shipping.

    ReplyDelete
  61. buy gym leggings for girls online from Dshred at best prices delivered at your doorstep. Get free shipping

    ReplyDelete
  62. buy skin care products in pakistan in Pakistan at best prices delivered at your doorstep. Get free shipping

    ReplyDelete
  63. buy gym leggings for women online from Dshred at best prices delivered at your doorstep. Get free shipping

    ReplyDelete
  64. Food is basic need of life. Buy baby food in pakistan at medisense with affordable prices. Shop Now!

    ReplyDelete
  65. Best mother care products are available at medisense with affordable prices. Order Now.

    ReplyDelete
  66. Best Quality otc medicine in pakistan are available at medisense with affordable prices. Order Now.

    ReplyDelete
  67. Buy best quality life saving insulin pen price in Pakistan from medisense at best prices. Order Now!

    ReplyDelete
  68. We do extra care for your babies. Buy best quality baby care products online Pakistan at best prices from medisense. Order Now and Enjoy Free delivery

    ReplyDelete
  69. Buy best quality hair care supplements from medisense at best prices at your door step. Order Now!

    ReplyDelete
  70. Nice piece of content. Thanks for sharing. Also visit saffuse

    ReplyDelete
  71. buy sports tshirts online from dshred at easy prices delivered at your doorstep. get free shipping

    ReplyDelete
  72. Need comfortable trousers for men? The wait is over because we have got the best men trousers to range only at Ndure. Just log onto our site to avail the best trousers made in Pakistan. We guarantee that you will not regret it. Breathable fabric, comfortable designs are going to be with you every second.

    ReplyDelete
  73. Visit women realistic fashion design and stuff zainab chottani

    ReplyDelete
  74. Buy luxury embroidered lawn collection online from Reign at best prices get free shipping

    ReplyDelete
  75. buy shorts men from ndure at easy prices get free shipping at your doorstep.

    ReplyDelete
  76. Buy electronics online from alfatah at best prices delivered at your doorstep. Get free shipping

    ReplyDelete
  77. Buy chino shorts online from ndure at best prices delivered at your doorstep. Get free shiping

    ReplyDelete
  78. Hi,
    Great post, thanks for sharing,
    Are you searching for the cbse school in greater noida west ? The Manthan School is the best place to start your child’s education. The school has an international approach with an emphasis on Indian culture and heritage.

    ReplyDelete
  79. Best Gaming mouse and keyboard with gaming controls and speed control, lightweight and compact best gaming mouse available online.

    ReplyDelete
  80. Software Development Technologies

    ReplyDelete
  81. Buy heels for Women from ECS to make your footwear fancy on every occasions.

    ReplyDelete
  82. ECS is offering the best simple dresses for women to make their collections fashionable. ECS fabrics are premium quality which make your outfit fancy.

    ReplyDelete
  83. What happens when your MERGE statement INSERTs a row that now satisfies the ON constraint.
    visit Tel-U

    ReplyDelete
  84. What happens when your MERGE statement INSERT

    ReplyDelete
  85. Prices and trade in motorcycles in Bangladesh for 2023 may vary depending on the make, model and region. For the most up-to-date and accurate information, visit the manufacturer's website and contact your local dealer. Find more information at Bike Price in Bangladesh.

    ReplyDelete