Skip to content

Upserts

2012 January 23
tags:
by Shannon Lowder

Up to this point in our design patterns sessions we’ve concentrated on removing duplicates from our troubled table.  Now, let’s look into preventing those duplicates from being inserted in the first place.  If you haven’t heard the phrase upsert before, it’s the idea of doing an INSERT and UPDATE in the same statement, that way you handle changing data and inserting it in one go.  It’s very helpful for preventing duplicate data from being inserted.

CALGARY, ALBERTA 1916 ---DUPLICATE DOG LICENSE #217

For this article we’re going to use T-SQL to prevent these duplicates, rather than establishing a constraint, and then building an error handler when we try to insert duplicate data.  Let’s dig in.

CREATE TABLE [dbo].[TroubledTable](
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [email] [varchar](255) NULL
)

Consider our simple troubled table.  This little table has done a good job of collecting contact information so far, we decide, we have no good reason to change the structure, we just want to make it so if a user wants to enter the same row twice, it won’t actually INSERT into the table.  If we use an IF THEN construct, we can check to make sure that row doesn’t already exist.  Once we know the record doesn’t exist, we can run the INSERT statement.  Here’s what that would look like.

IF NOT EXISTS (
   SELECT FirstName
   FROM TroubledTable
   WHERE
      FirstName = <value>
      AND LastName = <value>
      AND email = <value>
)
   INSERT INTO TroubledTable
   (FirstName, LastName, email)
   VALUES <value>, <value>, <value>

Pretty easy right?  Now this solution doesn’t really allow us to update information.  If we wanted to allow updates, we could add a primary key to the table, so we can uniquely identify each row, we could change the construct to search for a primary key, and when found UPDATE, otherwise it runs an INSERT.  In this new scenario we could have two people with the same name, but no two users are allowed to have the same email address.

First we set up a variable to hold the primary key in the case we want to do an update.

DECLARE @PKToUpdate INT

Next, we’re going to look for a value for @PKToUpdate.

SELECT @PKToUpdate = ttPrimaryKey
FROM TroubledTableWithPK
WHERE
    email = <value>

If we find no rows, then @PKToUpdate will be NULL.  So we can use that knowledge to build our upsert.

IF (@PKToUpdate IS NOT NULL)
    UPDATE TroubledTableWithPK SET
        FirstName = <value>
        , LastName = <value>
        , email = <value>
    WHERE
        ttPrimaryKey = @PKToUpdate
ELSE     
    INSERT INTO TroubledTable
    (FirstName, LastName, email)
    VALUES <value>, <value>, <value>

Easy, right?  Either of these methods work really well for the single row at a time system, such as a web form built for one user at a time to come in and register.  But what if you had a system that allowed bulk inserts?

This is where the MERGE statement shines.  This is a much more elegant solution to handling all three operations in a single statement: INSERT, UPDATE, and DELETE.  If you haven’t seen this one before, don’t worry about it.  It’s all concepts you already know, just put together in a new way!  Check it out.

We first have to think about our data as having two tables: a source table and a target.

In our example we’re going to use my OriginalTroubledTable, a backup I made of TroubledTable as my source. It has the same structure as TroubledTable, so it makes things a bit easier. Now that we have a source and a target, we have to decide how we’ll match rows between the two. Just like a JOIN statement. When doing UPSERTS, you’re generally going to want to want the JOIN (or match) criteria to be the information that makes each row unique. In our case we don’t want duplicate email addresses. So we’re going to join on email from each table.

So far we have this much of our MERGE statement written by coming up with the source, target and JOIN criteria.

MERGE INTO TroubledTable AS [target]
USING OriginalTroubledTable as [source]
ON [target].email = [source].email

Next, we have to decide what we’re going to do when we have a match, when we don’t have a match in the target, and what we want to do if there’s no match in the source. All three of these are things you can handle with the MERGE statement.

So, in the case where we have a match on our join criteria, we will want to do an UPDATE. If we don’t have a match in the target, we’re going to do an INSERT.  If we don’t have a match in the source, we’re not going to do anything, so at least that’s easy!

Here’s how we construct that MERGE statement:

MERGE INTO TroubledTable AS [target]
USING OriginalTroubledTable as [source]  
   ON [target].email = [source].email 
WHEN MATCHED THEN
    UPDATE SET [target].FirstName = [source].FirstName
        , [target].LastName = [source].LastName
WHEN NOT MATCHED BY TARGET THEN
    INSERT (FirstName, LastName, email)
    VALUES ([source].FirstName, [source].LastName, [source].email);

So when we run this, any records missing from the target are added. Any records that already exist will have their first and last names updated. And we’re all good right?

Msg 8672, Level 16, State 1, Line 1 The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows. 

That’s right, Merge doesn’t handle it if you pass it if your source table has duplicates in it! You might want to check out my de-duping articles.

Either that, or you could use a sub query for the source.

MERGE INTO TroubledTable AS [target]
USING ( 
	SELECT DISTINCT FirstName, LastName, email
	FROM OriginalTroubledTable) as [source]
	
ON [target].email = [source].email --now we're only looking to make emails distinct
WHEN MATCHED THEN
	UPDATE SET [target].FirstName = [source].FirstName
		, [target].LastName = [source].LastName
		--no need to include email, since that's our join criterion
WHEN NOT MATCHED BY TARGET THEN
	INSERT (FirstName, LastName, email)
	VALUES ([source].FirstName, [source].LastName, [source].email);

Learning to read between the lines when you get a SQL error will go a long way in your career! This is just one chance to do that.

These are my two patterns for dealing with upserts, If you have another way, please let me know! I’m always interested in seeing new ways to do things.

As always if you have any questions about this, or anything else in SQL, let me know! I’m here to help.

One Response leave one →

Trackbacks and Pingbacks

  1. Design Patterns Session | Shannon Lowder

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS