SQL: If Exists Update Else Insert

MSDN Blog Postings » SQL: If Exists Update Else Insert

MSDN Blog Postings » SQL: If Exists Update Else Insert — Mon, 18 Feb 2008 06:41:51 GMT

PingBack from http://msdnrss.thecoderblogs.com/2008/02/17/sql-if-exists-update-else-insert/

re: SQL: If Exists Update Else Insert

jackbond — Mon, 18 Feb 2008 07:31:02 GMT

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.

re: SQL: If Exists Update Else Insert

Jeremiah.Clark — Mon, 18 Feb 2008 23:53:22 GMT

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."

re: SQL: If Exists Update Else Insert

Jeremiah.Clark — Tue, 19 Feb 2008 00:11:20 GMT

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.

Jeremiah

re: SQL: If Exists Update Else Insert

Andrew Herron — Thu, 21 Feb 2008 20:37:01 GMT

Will this code work for any SQL compliant server?

re: SQL: If Exists Update Else Insert

Jeremiah.Clark — Thu, 21 Feb 2008 22:19:39 GMT

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"

re: SQL: If Exists Update Else Insert

Guy — Thu, 21 Feb 2008 22:26:43 GMT

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.

re: SQL: If Exists Update Else Insert

Richard Ayotte — Thu, 21 Feb 2008 22:42:30 GMT

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.html

re: SQL: If Exists Update Else Insert

Jeremiah.Clark — Thu, 21 Feb 2008 22:53:10 GMT

Richard,

Thanks for the comment. I was not aware of the REPLACE statement in MySQL.

Looking at the documentation, it does effectively do the same thing. But it seems to do it in a different manner. If a row with the same PK or UNIQUE index exists, it deletes the old row and then inserts the new row. For anything that does not already exist in the table, it inserts.

I don't know if the deleting is more or less efficient than doing an update in MySQL. I would advise trying both ways and then compare the performance to see which solution better fits your needs.

re: SQL: If Exists Update Else Insert

Jeremiah.Clark — Thu, 21 Feb 2008 22:57:53 GMT

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.

re: SQL: If Exists Update Else Insert

JohnEric — Fri, 22 Feb 2008 00:52:40 GMT

RE: @@identity

You can use Scope_Identity() instead. That guarantees the correct value.

re: SQL: If Exists Update Else Insert

Marc Brooks — Sat, 23 Feb 2008 02:27:50 GMT

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 = @ItemID

re: SQL: If Exists Update Else Insert

Gabriel O. Perez — Mon, 03 Mar 2008 22:09:29 GMT

This is a great approach that I never thought of, and the only concern I had was answered within the comments section. Thanks!

Interesting Finds: 2008.02.23

gOODiDEA — Tue, 04 Mar 2008 04:45:14 GMT

Debug:DebuggingIIS7.0WebapplicationsremotelywithVisualStudio2008EmbeddingASP.NETServer...

re: SQL: If Exists Update Else Insert

Joe Davies — Fri, 04 Apr 2008 23:09:44 GMT

This is great! I shaved lots of time off my bulk insert/updates.

Thanks!

re: SQL: If Exists Update Else Insert

Travis Nelson — Wed, 04 Jun 2008 18:06:47 GMT

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!

re: SQL: If Exists Update Else Insert

Geoff Winkless — Wed, 16 Jul 2008 15:22:21 GMT

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.

re: SQL: If Exists Update Else Insert

John — Mon, 10 Nov 2008 19:07:37 GMT

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?

re: SQL: If Exists Update Else Insert

Brad — Tue, 16 Dec 2008 22:12:38 GMT

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.

re: SQL: If Exists Update Else Insert

PAKO — Thu, 22 Jan 2009 02:59:26 GMT

I CANTO USE DE IF CONDITION IN MYSQL SERVER 5.0

re: SQL: If Exists Update Else Insert

Martin — Thu, 19 Feb 2009 16:44:01 GMT

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?

re: SQL: If Exists Update Else Insert

Mithun — Wed, 25 Mar 2009 10:52:42 GMT

How does this behave in a multi-threaded environment? How to solve in that case?

re: SQL: If Exists Update Else Insert

Terry — Fri, 15 May 2009 19:12:48 GMT

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

re: SQL: If Exists Update Else Insert

Karthik — Wed, 10 Jun 2009 15:15:36 GMT

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..

re: SQL: If Exists Update Else Insert

Karim Sultan — Sat, 27 Jun 2009 09:19:24 GMT

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.

re: SQL: If Exists Update Else Insert

Pete — Wed, 16 Sep 2009 15:17:03 GMT

This is a great tip no doubt, however one observation is, it is being compared with bad code in the first instance.

Here's the original example

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')

UPDATE Table1 SET (...) WHERE Column1='SomeValue'

ELSE

INSERT INTO Table1 VALUES (...)

yes this will be slow. The major error is in the select * statement.

Most Developers know not to use this, but for the wrong reasons.

It's not because there are more columns returned and therefore bytes per row (which is true, but has minimal effect for most queries based on normailsed tables)

No, the real problem is Select * forces sql to tablescan unless every column returned is in an index. In other words, every row in the table is read - that's why it's slow.

If you use Select 'PrimaryKeyColumn' or Select 'IndexedColumn(s),....' performance is improved by a huge factor.

Just try it on a large table with a bulk update. or use SLQ profiler and view the queryplan - you'll see select * using 99% more resources when returning the exact same query (except for the columns) limited by the same where clause

Never the less, the solution offered by Jerimihah is a great idea and outside the box. I think adding an errorcheck as suggested by one DBA is also a good idea.

just my 2 cents worth

re: SQL: If Exists Update Else Insert

pooja — Thu, 01 Oct 2009 11:05:22 GMT

this is not working on mysql..plz comment why

re: SQL: If Exists Update Else Insert

chris — Mon, 16 Nov 2009 08:38:51 GMT

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?

re: SQL: If Exists Update Else Insert

Jeremiah.Clark — Mon, 30 Nov 2009 07:14:23 GMT

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.

re: SQL: If Exists Update Else Insert

emmy — Thu, 17 Dec 2009 09:18:21 GMT

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

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

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 Out

re: SQL: If Exists Update Else Insert

Brian — Thu, 17 Dec 2009 19:46:03 GMT

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!

Brian

re: SQL: If Exists Update Else Insert

emmy — Sat, 19 Dec 2009 08:15:29 GMT

but i'm not using my sql.it's sql server 2005!.