.NET Tips & Tricks Community RSS

Those who prefer reading this site through RSS now can also subscribe for updates to .NET Tips & Tricks Community.

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.

P.S. both methods should be used in transaction with isolation level Serializable.

11/17/2007