The Disturbed Buddha

Simple Observations of a Self-proclaimed Novice

Easy SQL “If Record Exists, Update It. If Not, Insert It.”

A very common scenario is the one where we want to update the information in a record if it already exists in the table, and if it doesn’t exist, we want to create a new record with the information. 

The most common solution to this problem is using IF EXISTS  (subquery).  This comes to mind first because it matches how we think about the problem (as you can see by the title of this article!).  We say “If the criterion exists in this initial subquery, then I’ll do this.  If not, I’ll do this other thing.”  This results in a three-step process:

  1. Do the subquery (SELECT whatever FROM wherever WHERE something).
  2. Evaluate the EXISTS statement (is it there or not?).
  3. Execute either the UPDATE or INSERT statement.

Now, let’s try an “unnatural” shortcut.  I say unnatural because it doesn’t follow that “natural” logic occurring in our brain that I mentioned above.  Instead, let’s just do the update, and if it fails, then we’ll do the insert.  When the update fails, that just means that no rows were affected, not that an error was thrown.  Now we are down to a one-step (if the update succeeds) or two-step process (if we have to insert instead).  This is much more efficient!


This is not necessarily a practical example, but let’s say that we have a table called “Users” which has three fields:  “UserID”, “FirstName”, and “LastName”.  If a record already exists with the specified UserID, simply update it with the new @FirstName and @LastName values.  If it does not exist, create a new record with those values.

     @UserID AS int, 
     @FirstName AS varchar(50), 
     @LastName AS varchar(50) 
          DECLARE @rc int    

          UPDATE [Users] 
             SET FirstName = @FirstName, LastName = @LastName 
           WHERE UserID = @UserID   

          /* how many rows were affected? */ 
          SELECT @rc = @@ROWCOUNT    

          IF @rc = 0 
                    INSERT INTO [Users] 
                                (FirstName, LastName) 
                         VALUES (@FirstName, LastName) 


November 29, 2007 - Posted by | SQL, Web Development

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: