Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

SQL Server: Best way to Update row if exists, Insert if not

This question pops up a lot everywhere and it's a common business requirement and until SQL Server 2008 doesn't come out with its MERGE statement that will do that in one go we're stuck with 2 ways of achieving this. The biggest problem with every update/insert (upsert for those who haven't heard the term yet) is locking.

Each DML statement basicaly contains 2 locks. IX Lock (Intent Exclusive Lock) and X Lock(Exclusive Lock).

When the SQL Server searches for the data to modify it takes IX lock on the whole page or table and then it takes an X Lock on the rows to be modified.

 

Our goal here is to minimize anykind of locks. As i said earlier there are two methods of doing this:

1. Update … if rowcount = 0 insert

2. If row exists update else insert

 

First let's create our test table:

USE tempdb

IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1 GO CREATE TABLE t1 (id INT PRIMARY KEY, name1 VARCHAR(10)) INSERT INTO t1 SELECT 1, 'name 1' UNION ALL SELECT 2, 'name 2' UNION ALL SELECT 3, 'name 3' UNION ALL SELECT 4, 'name 4' UNION ALL SELECT 5, 'name 5' GO

Both methods when Inserting a row:

————————-
– I N S E R T    R O W —
————————-
DECLARE @rc INT

– NO EXISTS ———— BEGIN TRAN – update the row with id = 6. UPDATE t1 SET name1 = 'name 6' WHERE id = 6 – get rowcount SELECT @rc = @@ROWCOUNT – see what locks are being held SELECT resource_type, request_mode, resource_description, 'ROW NOT IN TABLE - No Exists - after update' FROM sys.dm_tran_locks – if update rowcount = 0 means that there is no such row so we insert it IF @rc = 0 BEGIN INSERT INTO t1 SELECT 6, 'name 6' – see what locks are being held SELECT resource_type, request_mode, resource_description, 'ROW NOT IN TABLE - No Exists - after insert' FROM sys.dm_tran_locks END ROLLBACK

– EXISTS ————— BEGIN TRAN – if row already exists in a table update it else insert new row IF EXISTS (SELECT * FROM t1 WHERE id = 6) BEGIN – see what locks are being held from select SELECT resource_type, request_mode, resource_description, 'ROW NOT IN TABLE - Exists - after select' FROM sys.dm_tran_locks

<span class="kwrd">UPDATE</span>  t1
<span class="kwrd">SET</span>     name1 = <span class="str">&#39;name 6&#39;</span>
<span class="kwrd">WHERE</span>   id = 6

<span class="kwrd">SELECT</span>  resource_type, request_mode, resource_description, <span class="str">&#39;ROW NOT IN TABLE - Exists - after update&#39;</span>
<span class="kwrd">FROM</span>    sys.dm_tran_locks

END ELSE BEGIN – see what locks are being held from select SELECT resource_type, request_mode, resource_description, 'ROW NOT IN TABLE - Exists - after select' FROM sys.dm_tran_locks

<span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> t1
<span class="kwrd">SELECT</span>  6, <span class="str">&#39;name 6&#39;</span>

<span class="kwrd">SELECT</span>  resource_type, request_mode, resource_description, <span class="str">&#39;ROW NOT IN TABLE - Exists - after insert&#39;</span>
<span class="kwrd">FROM</span>    sys.dm_tran_locks

END ROLLBACK

GO

When a row doesn't exist in a table we have to insert it.

The NO EXISTS method is more expensive due to the IX page lock by update and insert followed by X Lock by insert.

The EXISTS method uses a select * to check if the row exists using only an IS (Intent Shared) lock which is inexpensive compared to the IX lock made by the Update. This IS lock is then followed by an IX and X Lock made by the Insert.

 

Both methods when Updating a row:

————————-
– U P D A T E    R O W —
————————-
DECLARE @rc INT

– NO EXISTS ———— BEGIN TRAN

– update the row with id = 5 UPDATE t1 SET name1 = 'name 6' WHERE id = 5 – get rowcount SELECT @rc = @@ROWCOUNT – see what locks are being held SELECT resource_type, request_mode, resource_description, 'ROW IN TABLE - No Exists - after update' FROM sys.dm_tran_locks – update rowcount = 1 means that there is a row so we updated it IF @rc = 0 BEGIN INSERT INTO t1 SELECT 6, 'name 6'

<span class="rem">-- see what locks are being held</span>
<span class="kwrd">SELECT</span>  resource_type, request_mode, resource_description, <span class="str">&#39;ROW IN TABLE - No Exists - after insert&#39;</span>
<span class="kwrd">FROM</span>    sys.dm_tran_locks

END ROLLBACK

– EXISTS ————— BEGIN TRAN – if row already exists in a table update it else insert new row IF EXISTS (SELECT * FROM t1 WHERE id = 5) BEGIN – see what locks are being held from select SELECT resource_type, request_mode, resource_description, 'ROW IN TABLE - Exists - after select' FROM sys.dm_tran_locks

<span class="kwrd">UPDATE</span>  t1
<span class="kwrd">SET</span>     name1 = <span class="str">&#39;name 6&#39;</span>
<span class="kwrd">WHERE</span>   id = 5

<span class="kwrd">SELECT</span>  resource_type, request_mode, resource_description, <span class="str">&#39;ROW IN TABLE - Exists - after update&#39;</span>
<span class="kwrd">FROM</span>    sys.dm_tran_locks

END ELSE BEGIN – see what locks are being held from select SELECT resource_type, request_mode, resource_description, 'ROW IN TABLE - Exists - after select' FROM sys.dm_tran_locks

<span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> t1
<span class="kwrd">SELECT</span> 6, <span class="str">&#39;name 6&#39;</span>

<span class="kwrd">SELECT</span>  resource_type, request_mode, resource_description, <span class="str">&#39;ROW IN TABLE - Exists - after insert&#39;</span>
<span class="kwrd">FROM</span>    sys.dm_tran_locks

END ROLLBACK

GO

When a row exist in a table we have to update it.

The NO EXISTS method is less expensive due to only one IX and X Lock made by the Update.

The EXISTS method again uses a select * to check if the row exists using an IS (Intent Shared) lock followed by an IX and X Lock made by the Update.

Here the NO EXISTS is better because it doesn't need an IS (Intent Shared) lock.

 

However on overall lock use i much rather go with EXISTS method than NO EXISTS.

 

kick it on DotNetKicks.com
 

Legacy Comments


Jason Stangroome
2007-08-02
re: SQL Server: Best way to Update row if exists, Insert if not
How do you suppose the locking situation changes with the new MERGE statement in SQL Server 2008?
http://blog.benhall.me.uk/2007/06/sql-server-2008-sql-merge-statement.html

Mladen
2007-08-02
re: SQL Server: Best way to Update row if exists, Insert if not
i have no idea. hadn't had a chance to play with sql 2008 much.
try it and let me know.

noeld
2007-08-06
re: SQL Server: Best way to Update row if exists, Insert if not
In my opinion using EXISTS the way you are, is totally flawed on highly concurrent scenarios. Assume two connections; One is sending that code at the &quot;same time&quot; than the other is sending a &quot;delete or that same row&quot;. Because of the IS lock your &quot;EXISTS&quot; &quot;passes&quot; that check and the delete could wipe that record out ... can you guess what happens to the update statement ?

Cheers!

Mladen
2007-08-06
re: SQL Server: Best way to Update row if exists, Insert if not
well... do you have any better suggestion?

Saad Ahmad
2007-09-07
re: SQL Server: Best way to Update row if exists, Insert if not
First, both of these methods could theoratically give you a wrong error.

1. Update ... if rowcount = 0 insert

- Lets assume two processes P1 and P2
- Lets assume table TAB1 has row R1

Case 1.a (Row R1 does not exist)
a. P1 executes it at time T1 (executed update found that row does not exist)
b. P2 executes it at time T1 (executed update found that row does not exist)
c. Both will try insert and one will fail.

Case 1.b (Row R1 exists )
No issue in this case.

2. if row exists update else insert
will have the same issue as above.

Solution
---------
1. Table TAB1 should have a PK.
2. Try insert, then on exception of duplicate row, update.

Lets try our scenario:
Case 1.a (Row R1 does not exist)
a. P1 executes it at time T1 (insert will create the row)
b. P2 executes it at time T1 (insert is attempted, but because of P1 this will wait until P1 comits)
c. If P1 commits, P2 will detect duplicate and do the update. If P1 rolls back, P2's insert will succeed.

Case 1.b (Row R1 exists)
a. P1 executes it at time T1 (insert will detect a duplicate)
b. P2 executes it at time T1 (insert will detect a duplicate)
c. Both can do the update.


Don't think this can't happen; I actually noticied the issue in a high volume environment

Saad Ahmad
saad.ahmad@gmail.com

Mladen
2007-09-07
re: SQL Server: Best way to Update row if exists, Insert if not
i know it can happen.

as i said to the previous person who pointed this out:
shown me a better way.


Mladen
2007-09-07
re: SQL Server: Best way to Update row if exists, Insert if not
well exception handling is pretty heavy duty operation... i do see you point though

Mladen
2007-09-07
re: SQL Server: Best way to Update row if exists, Insert if not
i'd try to solve this with isolation levels...

Saad Ahmad
2007-09-07
re: SQL Server: Best way to Update row if exists, Insert if not
Isolation levels are not the right solution since the problem is in a high volume scenario where you do not want more isolation. The following code may work.

create table saad_test ( pk int not null primary key, data varchar(100) )

insert into saad_test values ( 1, 'saad' )

set nocount on
declare @v_pk int, @v_data varchar(100)
begin
set @v_pk = 1
set @v_data = 'ahmad2'
insert into saad_test values ( @v_pk, @v_data )
-- error for duplicate is 2627
if ( @@ERROR = 2627 )
update saad_test set data = @v_data where pk = @v_pk
end


And I will prefer to use true exceptions if available. But even those do not cause any unnecessary overhead. More importantly the code needs to work ...



Mladen
2007-09-07
re: SQL Server: Best way to Update row if exists, Insert if not
excellent! thanx!

i love when someone shows me a better way of doing things.

Jeff Moden
2007-09-08
re: SQL Server: Best way to Update row if exists, Insert if not
Ran into this because of another forum with a link... appologize if this comes out too negative, but...

Works great for a single row... what about batch merges? And, as clever as it is, updating by exception error just doesn't sit right with me... takes too long for large batches... insert has to be tried and failure detected and only then does the update occur. Probably alright for the RBAR necessary for a GUI, though.

Peter Larsson
2007-09-19
re: SQL Server: Best way to Update row if exists, Insert if not
Batch merges are easy in SQL Server 2008

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=87088

Roshni
2007-10-05
re: SQL Server: Best way to Update row if exists, Insert if not
I want to know how can I prevent inserting a row in a table if it already exists, and insert it when it does not exist. I am running a loop in PLSQL for Insert.

Mladen
2007-10-05
re: SQL Server: Best way to Update row if exists, Insert if not
have you tried the method described here?

ifo
2007-10-15
SQL Server: Best way to duplicate record
Hello,
Please I need advice.
SQL Server 2005. I have a table consisting of 200 fields with a primary key(auto-increment). I need to be able to duplicate via a stored procedure a specific row in the table identified by primary key value i.e. insert a record form that table into the SAME table .... the primary key of the inserted record so be incremented and I would need to change to of the field values in the inserted record (i.e. duplicating a row in a table with a primary key(auto increment). What is the best way to do this... one suggested writing to temp table then re-insert to original table. I have had some issues with this is there another method? Please not that other users may try and access the orginal record during this insert process. Can you give me example syntax and comments.
Thank You!

Mladen
2007-10-15
re: SQL Server: Best way to Update row if exists, Insert if not
insert into yourTable (columnsWithoutIdentity)
select columnsWithoutIdentity
from yourTable
where someCondition

Sam Saffron
2008-02-07
re: SQL Server: Best way to Update row if exists, Insert if not
Hi there,

Just a note that the method described here has some concurrency issues. I have described some ways around it on my blog at:
http://www.samsaffron.com/blog/archive/2007/04/04/14.aspx

Cheers
Sam

Mladen
2008-02-07
re: SQL Server: Best way to Update row if exists, Insert if not
this method can come in handy too:
http://www.sqlteam.com/article/application-locks-or-mutexes-in-sql-server-2005

Sinister China Penguin
2008-06-30
re: SQL Server: Best way to Update row if exists, Insert if not
Hi - thanks for this I found it useful for a small App I am working on at the moment.

It's a small point but it may be slightly more efficient to replace:

IF EXISTS (SELECT * FROM t1 WHERE id = 6)

With IF EXISTS (SELECT 1 FROM t1 WHERE id = 6)

just so the SQL engine doesn't have to go & fetch any data.

Thanks!

Mladen
2008-06-30
re: SQL Server: Best way to Update row if exists, Insert if not
hi,
acctually it doesn't matter if it's * or 1. sql server doesn't get any extra data than it needs in either case.

Tom
2008-08-19
re: SQL Server: Best way to Update row if exists, Insert if not
Throw an error??? Woa I never see something so ugly! Is anyone has a good solution, simple? Why its bad to test before if data already exists?

Miroslav Vracevic
2008-08-21
re: SQL Server: Best way to Update row if exists, Insert if not
So how about modifying Saad's example to look like this - no error handling here.
With few more modifications this can work for batch inserts/updates too

--prepare
create table mitch_test ( pk int not null primary key, data varchar(100) )
insert into mitch_test values ( 1, 'mitch' )


--insert/update block
set nocount on
declare @v_pk int, @v_data varchar(100)
begin
set @v_pk = 1
set @v_data = 'mitch2'
insert into mitch_test
select dt.*
from (select @v_pk pk, @v_data data) dt
left outer join mitch_test t on t.pk = dt.pk
where t.pk is null
if ( @@rowcount = 0 )
update mitch_test set data = @v_data where pk = @v_pk
end

aneef
2008-09-05
re: how to update every 10000 rows of a record...
hi,
Actually I want a sql statement to update evary 10000 rows of a record....
for eg...if a record consists of 30000 rows.....we created a new column in that record.....in that column we want to place the value 1 for first 10000 rows and the value 2 for next 10000 rows and the value 3 for next rows....and so on.....i.e., the value should be incremented by 1 for every 10000 rows....
please help in this as quickly as possible....

GAFEROZ
2008-09-23
re: SQL Server: Best way to Update row if exists, Insert if not
This reminds me about the good old times of COBOL. I'm glad technology has advanced this much since then...

darkdusky
2008-12-01
re: SQL Server: Best way to Update row if exists, Insert if not
How about trying update first then check if row exists and then do insert if needed.
The initial update would not lock any rows if the row didn't exist in the first place.
e.g.
Update .....
set .....=......
where id =@X

set @found=0
set @found = Select count(*) from ...... where id=@X

If @found =0
--do INSERT
--else exit

Mladen
2008-12-01
re: SQL Server: Best way to Update row if exists, Insert if not
that is also covered in the post.

sam
2009-02-01
re: SQL Server: Best way to Update row if exists, Insert if not
both of these methods are not save for concurrent updates see:

http://www.samsaffron.com/blog/archive/2007/04/04/14.aspx

MAHESH
2009-02-05
re: SQL Server: Best way to Update row if exists, Insert if not
i SOLVED my PROBLEM BY THIS ARTICLE

George
2009-03-14
re: SQL Server: Best way to Update row if exists, Insert if not
The best way of solving this probably depends on your situation. If you are going to do a lot of updating and it will be rare that the record does not exist, it is going to be best to try updating first and if no records are updated, insert one. This is faster in this scenario because the vast majority of the time it is just a single simple update. If you do this from your calling code rather than from SQL (though it is generally better to do everything in SQL), the insert statement would not even be compiled if the update worked.

escalera
2010-01-06
re: SQL Server: Best way to Update row if exists, Insert if not
does this procedure only works with primary keys columns?
Because I'm using a condition with 2 columns (receiptBal, receiptDate) in a table called RECEIPTS. But it seems like it's not doing what I want as it's inserting a new row even if it's already there.





DECLARE @rc INT
BEGIN TRAN

declare @receiptBal money, @receiptdate datetime, @receiptID int
set @receiptBal = '2.23'
set @receiptdate = '20091229'

insert receipts (receiptBal, StoreID, receiptDate)
select @receiptBal, 8, @receiptdate


SELECT @rc = @@ROWCOUNT

IF @rc = 0
BEGIN
update receipts
set receiptdate = @receiptdate
where receiptBal = @receiptBal
and receiptdate is null
end
COMMIT

Mladen
2010-01-06
re: SQL Server: Best way to Update row if exists, Insert if not
i think you have your conditions switched. you shoul dupdate forst then if @RC=0 insert

brs
2010-03-04
re: SQL Server: Best way to Update row if exists, Insert if not
How do we modify this when we are working with multiple threads and facing deadlock issue. Also I have a procedure which contains several such statements and I m facing the deadlock . Please let me know how this can be resolved

Mladen
2010-03-04
re: SQL Server: Best way to Update row if exists, Insert if not
either by using a serializable tran isolation level or by using sql server application locks
http://www.sqlteam.com/article/application-locks-or-mutexes-in-sql-server-2005

Priyavadan joshi
2010-08-16
re: SQL Server: Best way to Update row if exists, Insert if not
Best way to Update row if exists, Insert if not


Use This QUERY := >

use Condition IF Exise

EXAM :

IF Exists (select 1 From tblXyz With(nolock) Where ID =2 )
BEGIN
Update tblXyz
END
Else
Insert INTo tblXyz