Sometimes you want to add new rows to your database if they're not already
there. But if they already are you want to overwrite them with the new values
you provided. For example I love card games. But cards have a habit of going
missing over a time. So every now and then I need to add some more cards to my
deck. But the new ones tend to stick out just a little. Which makes bluffing...
Tricky!
So what I need to do is update all the existing cards to have the same
style. So I want to update some of the cards and insert others. It's time for an upsert!
You can approach an upsert in one of two ways: try to insert first then
update if the row already exists. Or the other way around. Update then insert if
the row isn't there. To reduce wasted effort you only want to do one of these.
But you often don't know in advance which you need to do. If you did you
could just write the insert or update as needed. So this leaves you with a tricky
decision: which do you try first? Insert or update? You want to put the most
likely action first. But this depends on your data.
For example if I've got no cards then when I buy my deck
everything is an insert. So it makes sense to do
this first. But a couple of months later when I need to replenish my deck I've
only got a couple of cards to insert and everything else is an update. So it makes
sense to do the update first. And this will be the case from now on. Unless that
is, someone steals my whole deck and we're back to everything being an insert again!
So the most likely operation can change over time without any warning. And
it's rarely as obvious as this which you want to do first. You can spend a lot of
effort trying to decide whether it's better to do insert first or update; only
to have this effort undone by changes in your data. Luckily there's a way out of this mess:
Merge!
This is one statement which takes
rows from one table and inserts or updates them into another based on a
join criteria. If the join finds a matching row it updates it. If not it
inserts. So you no longer need to decide which to do first. The join in merge must
link each row in the source to at most one row in the target. So you need to
join on a unique set of attributes. For playing cards that's the value and suit.
If there is a match we define what to over write in the when matched clause. And if
there isn't add new rows in the when not matched clause.
Each of these clauses is optional.
So you can have a merge which only does inserts or only does
updates. Which seems a bit pointless when you could just use plain old insert or
update, right? I mean personally I can't see the benefit of an insert only merge.
After all you can insert the results of a select.
But an update only merge can be useful in some cases.
If you want to update the rows in one table
using values from another often you need to run a correlated update. These can be
very slow to run because for each row in the target table you need to rerun the
subquery on the source. But with merge the database only needs to query each table
once. This can be a big time-saver so an update only merge can still be useful.
And the power of merge doesn't stop there. You can also add a where clause to
both the insert and the update causes so you don't change all the rows. For
example I really like the design of the jokers in this deck. So I want to keep them.
So to stop overwriting them you can add a check that the card isn't a joker.
Or maybe, because they're rarely used, I just want to get rid of them. To do this
add a delete statement to the when matched clause. But because this is in
the update section you can only change rows that already exist in the target
table AND have a matching row in this source. For example if my new deck has no
jokers in it then the updated merge won't touch the existing jokers in my
current deck. So they're not deleted. You can work around this but it requires
some complex SQL to do so. Check the links in the description if you want to
know more about this.
So that was a quick overview of merge, a handy way to do
upserts. This can run faster than an insert followed by a if-not-inserted
update or vice-versa. But if you're 99% or more sure that you'll always do
inserts or always do updates then it may be faster to write separate statements.
But as always test and verify on your data to be sure!
Thanks for watching!
Got any SQL topics you'd like us to create videos on?
Let us know in the comments and subscribe for future updates