SQL: If Exists Update Else Insert
This is a pretty common situation that comes up when performing
database operations. A stored procedure is called and the data needs
to be updated if it already exists and inserted if it does not. If we
refer to the Books Online documentation, it gives examples that are
similar to:
IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
This
approach does work, however it might not always be the best approach.
This will do a table/index scan for both the SELECT statement and the
UPDATE statement. In most standard approaches, the following statement
will likely provide better performance. It will only perform one
table/index scan instead of the two that are performed in the previous
approach.
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
INSERT INTO Table1 VALUES (...)
The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.
Just
remember, the examples in the MSDN documentation are usually the
easiest way to implement something, not necessarily the best way. Also
(as I re-learned recently), with any database operation, it is
good to performance test the different approaches that you take.
Sometimes the method that you think would be the worst might actually
outperform the way that you think would be the better way.
Comments
Anonymous
February 17, 2008
PingBack from http://msdnrss.thecoderblogs.com/2008/02/17/sql-if-exists-update-else-insert/Anonymous
February 17, 2008
Thanks for the tip, I have a lot of code with the inferior approach that I will be updating. Fortunately, SQL 2008's Merge statement will make this a non-issue.Anonymous
February 18, 2008
Sent to me by Chris: "Since I don't have a blog and you don't allow anonymous comments I thought I'd shoot a quick email with a question/concern. Regarding your post "SQL: If Exists Update Else Insert" with the alternative method of doing the Update and then checking the @@ROWCOUNT as to whether to perform an insert or not... I definitely would not have thought of it that way either. However, I was wondering, with the scope of @@ROWCOUNT being global is there the possibility that the @@ROWCOUNT value could be incorrect within the local scope if there is heavy use on the database? Or am I incorrect on the scope being global? The reason I thought of this goes back to the use of SCOPE_IDENTITY() over @@IDENTITY to get the last identity in the same scope. Thanks for your time."Anonymous
February 18, 2008
I have enabled anonymous comments. I thought that I had done that before, but I guess not. Chris, Great question! The scope of @@ROWCOUNT (or ROWCOUNT_BIG() in the case of a VERY large result) is limited to the current scope that contains the statement that was previously executed. So you are guaranteed the correct result when using @@ROWCOUNT. Like you mentioned, this is not the case with @@IDENTITY. You can get some really unexpected results if it is used improperly. JeremiahAnonymous
February 21, 2008
Will this code work for any SQL compliant server?Anonymous
February 21, 2008
Andrew, The basic idea should work with other types of SQL servers (however, I have not verified this). You should just be able to use the @@ROWCOUNT equivalent for the system that you are using. For example: MySql uses "row_count()" Oracle uses "sql%rowcount"Anonymous
February 21, 2008
If you are thinking of MySQL there is a function called ROW_COUNT() that serves the same purpose (available as of version 5). Theoretically Jeremiah's suggestion should result in improved performance on MySQL also, but you should test it to make sure.Anonymous
February 21, 2008
MySQL does something much better. It's not part of the standard but still very useful. REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... It does what you describe. If the record exists, it is updated, else inserted. http://dev.mysql.com/doc/refman/5.0/en/replace.htmlAnonymous
February 21, 2008
The comment has been removedAnonymous
February 21, 2008
Looking into the MySql documentation a little more I found this statement: INSERT ... ON DUPLICATE KEY UPDATE This seems to be closer to the upsert code that I initially wrote about.Anonymous
February 21, 2008
RE: @@identity You can use Scope_Identity() instead. That guarantees the correct value.Anonymous
February 22, 2008
I vastly prefer this sort of game, for example a HitCounter for some item that automatically inserts new rows houry: INSERT INTO dbo.HitCounter(ItemID, TimeSlot) SELECT TOP 1 @ItemID AS ID ,DateAdd(hh, DateDiff(hh, 0, GetUtcDate()), 0) AS TimeSlot FROM (SELECT 1 AS FakeColumn) AS FakeTable WHERE NOT EXISTS (SELECT * FROM dbo.HitCounter WHERE ItemID = @ItemID AND TimeSlot = DateAdd(hh, DateDiff(hh, 0, GetUtcDate()), 0)) UPDATE dbo.HitCounter SET Hits = Hits + 1 WHERE ItemID = @ItemID AND TimeSlot = DateAdd(hh, DateDiff(hh, 0, GetUtcDate()), 0) SELECT ID FROM dbo.HitCounter WHERE ItemID = @ItemIDAnonymous
March 03, 2008
This is a great approach that I never thought of, and the only concern I had was answered within the comments section. Thanks!Anonymous
March 03, 2008
Debug:DebuggingIIS7.0WebapplicationsremotelywithVisualStudio2008EmbeddingASP.NETServer...Anonymous
April 04, 2008
This is great! I shaved lots of time off my bulk insert/updates. Thanks!Anonymous
June 04, 2008
Thanks for the tip--just tried it and it worked perfectly. Is this more efficient than doing an "IF SELECT COUNT(*) FROM ... > 0 ELSE ... " That would select only one value, I'm just not fully sure of the overhead created when SQL Server executes the SELECT in this vs. an UPDATE that makes no changes. I'm not really sure how to really test this either and for my small app, it's really negligible anyway. Either way, yours worked seamlessly, so thanks!Anonymous
July 16, 2008
Using REPLACE in mysql is problematic because it DELETEs the existing row before INSERTing. The INSERT ... ON DUPLICATE KEY UPDATE you described is the better solution for mysql, until SQL2003's MERGE is implemented widely.Anonymous
November 10, 2008
What about the performance difference in doing a delete and insert vs. an update? I have an application that will probably always update 4 rows in the table (after the first iteration). Performance wise, should I do a delete for those four rows and then insert each row or simply do four updates?Anonymous
December 16, 2008
This was a great help Jeremiah! Our DBA recommended checking @@ERROR=0 in addition to @@ROWCOUNT=0 just in case the UPDATE was the right thing to do, but didn't work for whatever reason.Anonymous
January 21, 2009
I CANTO USE DE IF CONDITION IN MYSQL SERVER 5.0Anonymous
February 19, 2009
I'm trying to do this in a way that will work on SQL Server and DB2. Unfortunately DB2 is extremely primitive and as soon as you involve 'IF' you are into requiring a procedure, can't just run it in a script. The other side of the coin is that MERGER, which will do the job nicely on DB2, isn't supported in SQL Server before SQL 2008. Has anyone a solution that will run in a script on both DB2 and SQL Server 2005?Anonymous
March 24, 2009
How does this behave in a multi-threaded environment? How to solve in that case?Anonymous
May 15, 2009
My scenario was that I needed to SELECT a journal number (if it existed), otherwise create the journal number, and INSERT it. (I didn't need to do any updates in my scenario). Here is my quick solution:
select @JournalNumber = JournalNumber from JournalHeaderTable with (nolock) where BatchName = @BatchName IF @@ROWCOUNT=0 BEGIN exec taGetNextJournalEntry @O_vJournalEntryNumber = @JournalNumber output BEGIN TRANSACTION insert into JournalHeaderTable(BatchName, JournalNumber, Reference, TrxDate, TrxType) values (@BatchName, @JournalNumber, @Refrence, @TransactionDate, 0) COMMIT TRANSACTION END
Anonymous
June 10, 2009
In case of SQL 2008, we can use MERGE for this..just implemented that within my SSIS package to handle new and modified data, and it works like a charm..Anonymous
June 26, 2009
Hey thanks Jeremiah, for SQL 2005, your solution is the best I've seen yet (after trolling and review 3 other suggestions). I'll gladly give up portability (it's a quick port to MySQL and Oracle anyways) for a performance gain. In fact, it's not so much a performance gain as it is a reduction in queries (max 2 versus max 3) that makes it much more appealing over the long run. Thanks for the insight, and keep sharing.Anonymous
September 16, 2009
The comment has been removedAnonymous
September 30, 2009
this is not working on mysql..plz comment whyAnonymous
November 15, 2009
Hello, Is this code thread safe? Would... UPDATE Table1 SET (...) WHERE Column1='SomeValue' IF @@ROWCOUNT=0 INSERT INTO Table1 VALUES (...) ...need wrapping in a transaction? i.e if two processes read @@ rowcount as 0 (as the row didn't exist and then both try and INSERT you'd get an error? Or would SQL server run the whole statement as an atomic operation?Anonymous
November 29, 2009
chris, Although I cannot find the official documentation that backs this up, I am slmost 100% sure that @@rowcount is scoped to the query/session/connection that you are using. As far as I know, it is "thread safe" as pertaining to your question.Anonymous
December 16, 2009
hi Jeremiah, i have a problem inserting data only once. i knew that the data is inserted twice as i'm grasping these data from finger print device.for example i grasp data from 28/10/2009 from the buffer of the machine.i just want to compare datetime field in database with today's date.suppose min(datetime1) is 28/10/2009 and max(datetime1) is 13/12/2009 i want to insert 14,15 & 16/12.if tommorrow is 17/12/2009 . i tried to make condition to insert only today's date but it keeps inserting it.i mean i eant to make sure it's inserted only once.if it's exists i may make update to the existing data. i tried to make this procedure: it didnt work out. create procedure sp_attendance5 @EnrollNo int=null,@name1 varchar(20)=null, @datetime1 datetime=NULL,@check1 varchar(20)=null,@num int= null as begin IF EXISTS (select EnrollNo,name1,datetime1,check1 from attendance) update attendance set (EnrollNo=@EnrollNo,name1=@name1,datetime1=@datetime1,check1=@check1) where num=@num else if datetime1<=dateadd(dd,DATEDIFF(dd,0,getdate()),0) insert into attendance(EnrollNo,name1,datetime1,check1) values (@EnrollNo,@name1,@datetime1,@check1) end go the primary key in attendance database is Num it's auto increment Num,EnrollNo,name1,datetime1,check1 1 134 Adam Hurry 28/10/2009 14:29:49 check in 2 102 Noel Lipson 28/10/2009 17:21:12 check in 3 102 Noel Lipson 28/10/2009 17:21:19 Check OutAnonymous
December 17, 2009
I had a need for a similar use, but on MySQL. Instead of @@ROWCOUNT you can use ROW_COUNT(), but in using it, I revealed another interesting problem... Affected rows (in mySQL anyways) counts an affected row only if it CHANGES. This means that if a row exists and your update was successful, it may still return 0 affected rows. If you don't know if the row exists, chances are you might not know if it's changing either. This means this method may add a duplicate row anyways! At least this is what I encountered in mySQL. I would have liked to do it all in one Query, but until I know a way that works better, I'll have to settle! BrianAnonymous
December 18, 2009
but i'm not using my sql.it's sql server 2005!.Anonymous
February 28, 2010
Just for sake of completeness 2 things
- Isnt Update a Blocking call? In case the user expect most of the time Insert will happen he should go with if exists(select) insert else update. Also in some cases only an insert is required if record doesnt exist, the basic if not exists(select) insert , will work best
- but if most of the time update is expected the way you mentioned works better. Had been using these for past few months and one of my friend Just stumbled on your blog and even for the case where update is never expected or going to happen only 5-10% of the time, he used your way. So thought will mention these. Would love to know your views
Anonymous
March 09, 2010
Vibhore, While it is true that the update statement is doing nothing if you are just doing inserts, it is no less performant that using if (exists). In the case of an insert, both will perform one table scan (or index search) to perform the insert. In the case of Update first, it is essentially a no-op. The benefit is really seen when you are doing both inserts and updates, since the if (exists) method will perform 2 scans, whereas the update (@@Rowcount) method will only perform 1. So to sum up, if you know that you are only doing inserts, then this doesn't apply. But if you perform any updates in addidtion to inserts, then the reduced scans are quite beneficial. My advice would be to use SQL Profiler and run a sample set of data using both methods. Thanks for the comment.Anonymous
April 05, 2010
hi, your post is very helpful article as im working on same scenario. but im new to pl/sql can you please write full query.Anonymous
January 05, 2011
The comment has been removedAnonymous
January 24, 2011
Fortunately if you really kow your stuff, you'll have a logical mind to deduce such outcomes. Not claiming to have a logical mind like I hear most people, when really they can't work out simple problems. This is database querying, let's not get too carried away people. Quite frankly I can make my own data storage solutions that perform better than SQL anyway, just not as convenient!Anonymous
April 26, 2011
The comment has been removedAnonymous
August 03, 2011
Does SQLServer supports something like Syabse SQL Anywhere on existing update clause? If not, do you have a plan to implement it?Anonymous
December 04, 2011
MySQL has another approach for the same dev.mysql.com/.../insert-on-duplicate.htmlAnonymous
March 08, 2012
I love you ! you are great!Anonymous
June 25, 2012
Everything in one query with INSERT ... ON DUPLICATE KEY UPDATE (MySQL) dev.mysql.com/.../insert-on-duplicate.htmlAnonymous
August 21, 2012
Thanks , Nice postAnonymous
October 03, 2012
Cool ;P Thx you!Anonymous
November 29, 2012
great idea, thanksAnonymous
July 27, 2014
UPDATE Table1 SET (...) WHERE Column1='SomeValue' IF @@ROWCOUNT=0 INSERT INTO Table1 VALUES (...) Does it put the extra Load (pressure) on Transaction Log? Just because at time TWO statements gets logged in Transaction Log WHERE AS IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue') UPDATE Table1 SET (...) WHERE Column1='SomeValue' ELSE INSERT INTO Table1 VALUES (...) Please suggest???Anonymous
July 30, 2014
Exists any free tool that dynamically generate the "If Exist Update Else Insert" statement for all records of a specific table?Anonymous
February 12, 2015
Very simple and valuable post. Thank you JeremiahAnonymous
April 08, 2015
The comment has been removedAnonymous
July 09, 2015
Thank you Jeremiah, u are a true gentleman and a scholarAnonymous
October 14, 2015
The comment has been removed