How to update if row exists else insert in SQL Server

While developing an application with a database, frequently you will need to provide an option to update the data if a row exists else insert a row. This kind of operation is needed mainly for the master tables like the customer table, user table, etc. Developers use several workarounds to update if a row exists or else insert. The method I commonly use is IF EXISTS. Rarely I use the MERGE option. Here we will go through both the methods with an example.

Using IF EXISTS

This is my go to method for checking and updating or inserting records in a table. This is a straightforward method without any complications. Let us see how to use IF EXISTS … ELSE statement.

Syntax

IF EXISTS (SELECT * FROM MyTable WHERE ColID = @Id)
BEGIN
    -- Write your update statement here.
END
ELSE
BEGIN
   -- Write your insert statement here.
END
GO

Example

DECLARE @FirstName AS VARCHAR (50), 
	@MiddleName AS VARCHAR (50), 
	@LastName AS VARCHAR (50),
	@Address AS VARCHAR (250);

SELECT @FirstName = 'Jhonson', 
	@MiddleName = 'D', 
	@LastName = 'Rhodes', 
	@Address = 'Sydney, Australia';

IF EXISTS (SELECT * FROM [Purchasing].[People] 
			WHERE FirstName = @FirstName AND 
			MiddleName = @MiddleName AND 
			LastName = @LastName)
BEGIN
    UPDATE [Purchasing].[People] 
		SET Address = @Address 
		WHERE FirstName = @FirstName AND 
			MiddleName = @MiddleName AND 
			LastName = @LastName
END
ELSE
BEGIN
   INSERT INTO [Purchasing].[People] 
   (FirstName, MiddleName, LastName, [Address])
   VALUES (@FirstName, @MiddleName, @LastName, @Address)
END
GO

Using MERGE

MERGE is used to insert or update or delete records in a table based on one or more matching conditions. This method is not as simple or performance friendly as the IF EXISTS method. However, it is useful when you want to perform complex matching conditions. Here is an example of how to use MERGE to update if a record exists else insert a record.

SELECT * FROM [Purchasing].[People]
GO

DECLARE @FirstName AS VARCHAR (50), 
	@MiddleName AS VARCHAR (50), 
	@LastName AS VARCHAR (50),
	@Address AS VARCHAR (250);

SELECT @FirstName = 'Jhonson', 
	@MiddleName = 'D', 
	@LastName = 'Rhodes', 
	@Address = 'Sydney, Australia';

MERGE [Purchasing].[People] AS P
USING (SELECT @FirstName AS FirstName, 
		@MiddleName AS MiddleName, 
		@LastName AS LastName, 
		@Address AS Address) AS S
    ON P.FirstName = @FirstName AND 
			P.MiddleName = @MiddleName AND 
			P.LastName = @LastName
WHEN MATCHED THEN
    UPDATE SET Address = S.Address
WHEN NOT MATCHED THEN
    INSERT (FirstName, MiddleName, LastName, [Address])
    VALUES (@FirstName, @MiddleName, @LastName, @Address);
GO

SELECT * FROM [Purchasing].[People]
GO

Update If Row Exists Else Insert In SQL Server

Reference

MORE about EXISTS at Microsoft Docs.
More about MERGE at Microsoft Docs.