views:

5403

answers:

13

I've written a stored proc that will do an update if a record exists, otherwise it will do an insert. It looks something like this:

update myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)

My logic behind writing it in this way is that the update will perform an implicit select using the where clause and if that returns 0 then the insert will take place.

The alternative to doing it this way would be to do a select and then based on the number of rows returned either do an update or insert. This I considered inefficient because if you are to do an update it will cause 2 selects (the first explicit select call and the second implicit in the where of the update). If the proc were to do an insert then there'd be no difference in efficiency.

Is my logic sound here? Is this how you would combine an insert and update into a stored proc?

+19  A: 

Your assumption is right, this is the optimal way to do it and it's called upsert/merge.

Importance of UPSERT - from sqlservercentral.com:

For every update in the case mentioned above we are removing one additional read from the table if we use the UPSERT instead of EXISTS. Unfortunately for an Insert, both the UPSERT and IF EXISTS methods use the same number of reads on the table. Therefore the check for existence should only be done when there is a very valid reason to justify the additional I/O. The optimized way to do things is to make sure that you have little reads as possible on the DB.

The best strategy is to attempt the update. If no rows are affected by the update then insert. In most circumstances, the row will already exist and only one I/O will be required.

Edit: Please check out this answer and the linked blog post to learn about the problems with this pattern and how to make it work safe.

binOr
Also known as "Optimistic Insert". Definitely the right way to do it. :)
AJ
How can this answer be correct, it doesnt even answer the question, where is the code?
Sam Saffron
Well, it did at least answer one question, I think. And I didn't add code because the code in the question seemed right for me already. Though I would put it in a transaction, I did not take the isolation level into account for the update. Thanks for pointing that out in your answer!
binOr
@binOr, thanks, didn't mean to flame, just want to make sure the correct patterns are being used - out there ....
Sam Saffron
+5  A: 

MERGE is one of the new features in SQL Server 2008, by the way.

Jon Galloway
A: 

Yes, MERGE is what you are trying to do. Jon mentioned above its availability in SQL Server 2008. It's also been supported in Oracle since version Oracle9i.

Bernard Dy
+2  A: 

Today I learned my first slang SQL expression "UPSERT" - very cool - thanks binOr!

Also thanks for the heads-up on MERGE.

Guy
+7  A: 

If to be used with SQL Server 2000/2005 the original code needs to be enclosed in transaction to make sure that data remain consistent in concurrent scenario.

BEGIN TRANSACTION Upsert
update myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)
COMMIT TRANSACTION Upsert

This will incur additional performance cost, but will ensure data integrity.

Add, as already suggested, MERGE should be used where available.

Dima Malenko
This code is prone to deadlocks
Sam Saffron
+3  A: 

Big fan of the UPSERT, really cuts down on the code to manage. Here is another way I do it: One of the input parameters is ID, if the ID is NULL or 0, you know it's an INSERT, otherwise it's an update. Assumes the application knows if there is an ID, so wont work in all situations, but will cut the executes in half if you do.

Natron
Thanks Natron - in my use case the application does not keep track of the ID but I can see where some use cases could use this. Useful info - thanks.
Guy
A: 

As dmytro mentioned, this needs to be in a transaction to maintain integrity.

Nathan Skerl
A: 

Isn't this just one of those interesting SQL tricks? Why doesn't your app know if it is inserting or updating? Doesn't your table have a primary key column and wouldn't that be the indicator of what do do here?

John Dyer
A use case would be where a view count was updated in a table. If the ID wasn't already in the table then it'd need to be inserted with a view count of 1. Otherwise updated with view count incremented.
Guy
Why isn't the ID already in the table? Why wasn't it added when whatever is being viewed was created?
Duke
+1  A: 

Your logic seems sound, but you might want to consider adding some code to prevent the insert if you had passed in a specific primary key.

Otherwise, if you're always doing an insert if the update didn't affect any records, what happens when someone deletes the record before you "UPSERT" runs? Now the record you were trying to update doesn't exist, so it'll create a record instead. That probably isn't the behavior you were looking for.

-- Kevin Fairchild

Kevin Fairchild
+2  A: 

If you are not doing a merge in SQL 2008 you must change it to:

if @@rowcount = 0 and @@error=0

otherwise if the update fails for some reason then it will try and to an insert afterwards because the rowcount on a failed statement is 0

Simon Munro
+4  A: 

You not only need to run it in transaction, it also needs high isolation level. I fact default isolation level is Read Commited and this code need Serializable.

SET transaction isolation level SERIALIZABLE
BEGIN TRANSACTION Upsert
UPDATE myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
  begin
    INSERT into myTable (ID, Col1, Col2) values (@ID @col1, @col2)
  end
COMMIT TRANSACTION Upsert

Mayne adding also the @@error check and rollback could be good idea.

Tomas Tintera
A: 

here's a post that covers this from the locking perspective: http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx

This article is demonstrating patterns that are not safe for concurrent updates .. in particular you can get primary key violations
Sam Saffron
+10  A: 

Please read the post on my blog for a good, safe pattern you can use. There are a lot of considerations, and the accepted answer on this question is far from safe.

For a quick answer try the following pattern. It will work fine on SQL 2000 and above. SQL 2005 gives you error handling which opens up other options and SQL 2008 gives you a MERGE command.

begin tran
   update t with (serializable)
   set hitCount = hitCount + 1
   where pk = @id
   if @@rowcount = 0
   begin
      insert t (pk, hitCount)
      values (@id,1)
   end
commit tran
Sam Saffron