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

18 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