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

37 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. I would like to say that this article really convinced me, you give me best information!
    Y8
    Friv 3

    ReplyDelete
  3. I just want to say thanks for your wonderful post, it is contain a lot of knowledge and information that i needed right now. You really help me out my friend, thanks!
    halloween| animal jam| happy wheels| cool math games| 8 ball pool| sudoku| yoob| friv| tetris| friv4school| monster high

    ReplyDelete
  4. I am very happy to discover your post as it will become on top in my collection of favorite blogs to visit.
    Friv 4
    Friv 5
    Friv 100
    Happy Wheels

    ReplyDelete
  5. This was a really great contest and hopefully I can attend the next one. It was alot of fun and I really enjoyed
    Super smash flash 2|
    Super mario bros|
    Cooking games for girls

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

    ReplyDelete
  7. Do not know what to say, I liked your article and I hope you will have many entries or more.
    Friv 14|
    Kizi|
    Friv4school

    ReplyDelete
  8. when you see the stress in work, life, friv could well be a good site to try. thanks you for sharing friv games, friv online, friv.com, friv

    ReplyDelete
  9. Really impressive post. I enjoyed your article and planning to rewrite it on my own blog
    Friv 1
    Juegos Friv 3

    ReplyDelete
  10. 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
  11. Play free Friv games flash online games flash.
    Thanks you for sharing
    Friv Games | Friv Online

    ReplyDelete
  12. You need to kill time, you need entertainment. Refer to our website. hope you get the most comfort.
    Thanks you for sharing!
    Friv
    Friv Games
    Yepi 4
    Kizi 4

    ReplyDelete
  13. I would very much like to agree with the previous commenter! I find this blog really useful for my uni project. I hope to add more useful posts later.
    Kizi 2
    Friv 2

    ReplyDelete
  14. Thanks for sharing. I hope it will be helpful for too many people that are searching for this topic.
    Friv 1000

    ReplyDelete
  15. You click here to discover interesting things decent thanks alot
    Friv 234

    ReplyDelete
  16. 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
    minecraft games
    g9g games
    a10 games
    yepi games
    hopy games
    kizi games
    friv games
    huz games
    4223 games
    kizi new games

    ReplyDelete
  17. You need to kill time, you need entertainment. Refer to our website. hope you get the most comfort.
    Thanks for sharing !
    Friv 5
    Kizi 1
    Yepi 3

    ReplyDelete
  18. after hours of work tired, you need entertainment. Refer to our website. hope you get the most comfortable
    Kizi 1000

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

    Cheap Dissertation Writing Service

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

    ReplyDelete
  21. Busy life circumstances than the current world history.
    Mario , Friv , Doraemon Games , Kizi , Yoob Juegos

    ReplyDelete
  22. Thanks for posting this useful info. It really helped me!!!
    Friv
    Kizi 4
    Friv Games

    ReplyDelete
  23. friv games
    Friv is big derectory flash games online free for kids. My site add new-best games from Friv, Kizi, Y8, Yepi... Visit and play now, free for fun at Friv.wf!

    ReplyDelete
  24. The best space for your child to relax!: wingsio
    slitherio

    ReplyDelete
  25. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work.

    Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    Sign in to Gmail to access to all Gooogle services. Log in to your account or sign up to create a new account
    gmail sign in

    gmail log in

    what's gmail?

    slitherio

    Tank trouble

    happy wheels

    Strike Force Heroes

    tank trouble 2

    In this game, you start at the cavern men's age, then evolve! There is a total of 5 ages, each with its units and turrets. Take control of 16 different units and 15 different turrets to defend your base and destroy your enemy.
    age of war

    We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work.
    happy wheels

    strike force heroes

    earn to die

    Fireboy and Watergirl arrived again to the temple in the forest. 2 players together can help them to find their way out.
    fire boy and water girl

    ReplyDelete
  26. Play the Best Free Games! We've picked out the racing games, cooking games, candy crush, games shooting, fashion games, ...
    Thanks for sharing !
    Kizi 200
    Friv 1
    Y88
    Y9

    ReplyDelete
  27. The war between humans, orcs and elves continues. Lead your race through a series of epic battles, using your crossbow to fend off foes and sending out units to destroy castles. Researching and upgrading wisely will be crucial to your success!
    age of war 3 | age of war 2 | unfair mario 2 | tank trouble 3 | tank trouble
    The game controls are shown just under . Movement mechanisms primarily include acceleration and tilting controls.
    cubefield | tank trouble | happy wheels | slitherio | gold mine

    ReplyDelete
  28. I want you to thank for your time of this wonderful read!!! I definately enjoy every little bit of it and I have you bookmarked to check out new stuff of your blog a must read blog!
    slither io | wings io | science kombat | tank trouble 4

    ReplyDelete
  29. The blog or and best that is extremely useful to keep I can share the ideas
    of the future as this is really what I was looking for, I am very comfortable and pleased to come here. Thank you very much.
    earn to die 3| earn to die 6
    earn to die 5| happy wheels
    strike force heroes| slitherio
    earn to die 5| good game empire |tank trouble 2 | game earn to die

    ReplyDelete