.NET Tip of The Day: Two approaches to update database row if exists, insert if not
Two approaches to update database row if exists, insert if not
The biggest challenge with update/insert (so called upsert) is to minimize any kind of locks. Unfortunately there is no silver bullet for this yet. So let's review two the most commonly used methods:
1. Update, if @@ROWCOUNT = 0 then insert
UPDATE Table1 SET Column1 = @newValue WHERE Id = @id
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO Table1 (Id, Column1) VALUES (@id, @newValue)
END
This method is good if you know that in most of the cases a row will exist and update will be performed. Otherwise the second method should be used.
2. If row exists update, otherwise insert
IF EXISTS(SELECT * FROM Table1 WHERE Id = @id)
BEGIN
UPDATE Table1 SET Column1 = @newValue WHERE Id = @id
END
ELSE
BEGIN
INSERT INTO Table1 (Id, Column1) VALUES (@id, @newValue)
END
This one is good if you know that in most of the cases a row will not exist and insert will be performed. For such cases it executes SELECT statement followed by INSERT statement. That results in less expensive lock comparing to UPDATE + INSERT in previous method.
