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:
- Do the subquery (SELECT whatever FROM wherever WHERE something).
- Evaluate the EXISTS statement (is it there or not?).
- 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!
Example:
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.
CREATE PROCEDURE dbo.spAddUserName
(
@UserID AS int,
@FirstName AS varchar(50),
@LastName AS varchar(50)
)
AS
BEGIN
DECLARE @rc int
UPDATE [Users]
SET FirstName = @FirstName, LastName = @LastName
WHERE UserID = @UserID
/* how many rows were affected? */
SELECT @rc = @@ROWCOUNT
IF @rc = 0
BEGIN
INSERT INTO [Users]
(FirstName, LastName)
VALUES (@FirstName, LastName)
END
END