Video Transcription

Welcome back everyone. In this video, we’re going to be taking a look at the MERGE statement as part of our data modification video series. So what data modification, remember, we’ve covered so far inserts, updates, and delete. But now we’re going to look at a way to combine those operations, The MERGE statement is going to allow you to both insert and update in a single statement. So the syntax is a little bit more complicated here. So we as follows We have merge and then the table that we’re targeting, and then using the source table, so let’s take a look at the syntax for merge. So the syntax here is a little bit more complicated than some of the other statements that we’ve seen so far. But we’ll go through some examples and see how it looks. But merge on the target table. So we’re merging two, using this table on this predicate. And so when this predicate finds a match, right when matched, do this, when not matched, do this. So we can do an update or delete here when matched or not matched, and vice versa, when matched, and when not matched are optional statements. So these are optional, but we’ll show some examples of how this works. And General. The statement here can include predicates for evaluating matches. This is mostly for verifying if changes actually happened as a result of some operation. And note that I’m actually saying matched here. For my predicate, we have this on keyword which is very similar to what we did with joins, right, and inside of a join that was defining the filter. But here, we’re not actually filtering any rows out, it’s determining whether or not a row matches or does not match the predicate. But it’s not necessarily filtering rows. So the operation is slightly different. But let’s take a look at an example of what merge looks like. So again, also, please make sure before, if you’re following along here, please make sure you run the setup query for the this video. And then let’s take a look at our first example. So I’m actually going to use a CTE here, to showcase this. So with CTE, this is just pulling out the information about Marie Jones.

And so selecting that data from the table there, and then I have this MERGE statement down here. So merge person address. So this is my target table that I’m merging to using my CTE as the source of information source source CTE on s dot Person ID equals pa dot Person ID and address ID matches. So basically, I am my match condition is going to be the person ID from my CTE matches the person ID in the target table. And the the address ID or address type ID from the source, CTE matches the address type ID in the person address table. So again, these are matches not filter, so we’re not filtering rows, we’re matching rows. And so when a row matches, we do this statement. So we’re updating line one to line one, so on so forth, and updating the updated on. So if there is a match, I’m just updating the record in my table to be the record from the CTE the matching row in the CTE. When not matched, we’re going to insert right. So when there isn’t a match, we can actually pull from the target table. Right, but if, or and the source here, right, so we have no targets to actually compare to. So when we’re when we have a match, we can take the source, right, the source we go back here to the syntax here. When matched, we can update or delete the target table Using the source table, when we don’t have a match, then we can insert data into the target table from the source table. So here we are, when the records don’t match, so when the row from the source CTE source CTAs doesn’t match the record and the person address table, then we’re going to just straight out insert it. So that record essentially doesn’t exist. And so therefore, I’m going to insert it instead of updating it. So let’s go ahead and execute this. And that, and there we go. And so I’ll take myself off the screen here for a second. So we see, I am looking for Marie Jones, looking for Marie Jones. And we can actually see the record that we’ve updated. That’s the last two rows here, Marie Jones and Marie Jones down here. And we we have situations. Okay. So Marie Jones, originally, if we look back down here to our original table, right, only had one row, right, our original address table had only one row for Marie Jones. And she had a work address, but not a home address. And so up here, I gave my CTE both a work and a home address for the work address, I just updated it.

For the work address, I just updated it to be to be what was in the matching row here that now matches this row. So because the name and the address type matched, and so this is now PO Box 123, at Sunny Hill, which changed, right this was this is snowy drive. And so I changed her work address. And then I inserted a new record for Marie for her home address. So remember, right remember, the the merge predicate on Person ID equals Person ID and address type equals address type. This is not a filter, this is not a filter, it is a matching clause. So if and that only determines right, this match this predicate here determines on if we if we run the matched or not matched. And again, these are optional. So I can I don’t have to have a not match action. And I don’t have to have a matched action, I can have one or the other or both. I can have one or the other, or both. So all of these other rows, these, this first five, these first five rows in my table, were completely ignored for four matches because they did not match the person ID for Marie Jones, right, because all of these people appear were not Marie Jones, so therefore they did not get updated or records were not inserted on their behalf. So let’s use us check out a another example here. So similar CTE, just a different, I’m changing her work address back or home address back and work address back. Let’s go ahead and execute this. And I have when matched and the and the values in the column, write differ, then update, write else but not match just insert. So the not match part is still the same. But the new part here is that I have a predicate for my matched. Now I have a predicate for my matched. So again, the original predicate and the USING clause is do the record does the record from the source match what is in the target? If it does, then I’m going to check the this predicate. And if this predicate is true, then I’m going to do the update only if this predicate is true. So let’s go ahead and execute this. Oh, zero rows affected zero rows affected. So we have an error somewhere. All right.

So really, we have a the primary issue is we have nullable columns. We have nullable columns. And so With that, and if we go back here to look at our address table, person address change person address. So, line one is not null, line two is no. So that’s knowable. Really, really the difference is here, I can actually showcase you this, this statement here. This code is exactly the same as I just showed. But the difference is that instead of having no here, I’m actually providing a suite a for my line to have my address. And if we run this, again, though, we get zero rows affected zero rows affected, because we still don’t have a match, right? We still don’t have oh, well, we have a match. But our predicate is invalid, our predicate is invalid, primarily because we have this issue. Here, we have this issue here. Right? If we look at our duty to do so let me just do a select, sorry, showcases select and star from demo dot person address. And then if we go down here to Marie, line two, in both cases are not line two in both cases are null. And so even if we provide, even if we provide something right, remember comparisons to know Boolean comparisons to know do not work in SQL, because it becomes a three value predicate logic, meaning that we have true false and unknown. But when we are comparing something against No, it’s going to be unknown, because we don’t know what what the result is because the value is absent. So a correction to this. A correction to this is to use something like this. Correct. A correction is something like this. So when we have knowable columns, and we’re comparing something and getting small, we need to use the is operator, right? Is no or is not No, right is no or is not no. And so here, if our line two is no and line two is not and T dots are CTE and R. So if our CTE line is null and our source, our target table is not null. Basically, what I’m doing here is, this is a fancy way of just me doing that, are they different, right? Are the two different if the line if the line two is different, and my target and my source, then I’m going to update so if we run this now. Uh huh, there we go, we get one row affected, which should now show this record down here being line two to being not null, there we go. So they’re sweet A. So when we have this record here, so here’s my sweet A.

So when we have the same person, so when the person ID and the address type matches, so when the person is Marie Jones, and we are looking at her worker address, and the values differ from the source and the target, then we’re going to update it to match what our source table is, or our source information. So let’s take a look at the same example here, but a little bit cleaner, because I can actually use our, our set operators to improve on that really long winded comparison of comparing whether or not the source and the target are the same or not. And so a really interesting way to do this is using the intersect set operator. So if the source and the target intersect, then we don’t update because they’re both the same. But if the intersection does not exist, meaning that they are different, then we update and so that is a really useful, useful way to do that. And if we run that, we can actually see the end result of this. Getting up Data is here. But that is our merge right? Our merge is going to allow us to combine, update or deletes along with an insert. So when matched, we can either update or delete records. So the match between the source table matching the rec the row in the source table matching the row and the target table on this predicate, and then we can also perform an insertion if a match does not exist on the target.