I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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

    UPDATE  t1
    SET     name1 = 'name 6'
    WHERE   id = 6

    SELECT  resource_type, request_mode, resource_description, 'ROW NOT IN TABLE - Exists - after update'
    FROM    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

    INSERT INTO t1
    SELECT  6, 'name 6'
    
    SELECT  resource_type, request_mode, resource_description, 'ROW NOT IN TABLE - Exists - after insert'
    FROM    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'
    
    -- see what locks are being held
    SELECT  resource_type, request_mode, resource_description, 'ROW 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 = 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

    UPDATE  t1
    SET     name1 = 'name 6'
    WHERE   id = 5
    
    SELECT  resource_type, request_mode, resource_description, 'ROW IN TABLE - Exists - after update'
    FROM    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

    INSERT INTO t1
    SELECT 6, 'name 6'

    SELECT  resource_type, request_mode, resource_description, 'ROW IN TABLE - Exists - after insert'
    FROM    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
 

Print | posted on Monday, July 30, 2007 8:17 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# 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
8/2/2007 6:53 AM | Jason Stangroome
Gravatar

# 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.
8/2/2007 10:50 AM | Mladen
Gravatar

# 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 "same time" than the other is sending a "delete or that same row". Because of the IS lock your "EXISTS" "passes" that check and the delete could wipe that record out ... can you guess what happens to the update statement ?

Cheers!
8/6/2007 3:54 PM | noeld
Gravatar

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

well... do you have any better suggestion?
8/6/2007 3:55 PM | Mladen
Gravatar

# 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
9/7/2007 7:08 PM | Saad Ahmad
Gravatar

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

9/7/2007 7:13 PM | Mladen
Gravatar

# 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
9/7/2007 7:17 PM | Mladen
Gravatar

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

i'd try to solve this with isolation levels...
9/7/2007 7:24 PM | Mladen
Gravatar

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


9/7/2007 8:03 PM | Saad Ahmad
Gravatar

# 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.
9/7/2007 8:06 PM | Mladen
Gravatar

# 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.
9/8/2007 3:47 AM | Jeff Moden
Gravatar

# 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
9/19/2007 12:55 PM | Peter Larsson
Gravatar

# 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.
10/5/2007 7:59 AM | Roshni
Gravatar

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

have you tried the method described here?
10/5/2007 11:02 AM | Mladen
Gravatar

# 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!
10/15/2007 5:03 AM | ifo
Gravatar

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

insert into yourTable (columnsWithoutIdentity)
select columnsWithoutIdentity
from yourTable
where someCondition
10/15/2007 8:07 AM | Mladen
Gravatar

# 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
2/7/2008 12:07 AM | Sam Saffron
Gravatar

# 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
2/7/2008 10:52 AM | Mladen
Gravatar

# 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!
6/30/2008 4:04 PM | Sinister China Penguin
Gravatar

# 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.
6/30/2008 4:10 PM | Mladen
Gravatar

# 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?
8/19/2008 10:58 AM | Tom
Gravatar

# 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
8/21/2008 7:37 PM | Miroslav Vracevic
Gravatar

# 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....
9/5/2008 11:20 PM | aneef
Gravatar

# 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...
9/23/2008 10:12 PM | GAFEROZ
Gravatar

# 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
12/1/2008 3:58 PM | darkdusky
Gravatar

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

that is also covered in the post.
12/1/2008 4:00 PM | Mladen
Gravatar

# 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
2/1/2009 12:43 AM | sam
Gravatar

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

i SOLVED my PROBLEM BY THIS ARTICLE
2/5/2009 12:49 PM | MAHESH
Gravatar

# 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.
3/14/2009 6:03 PM | George
Gravatar

# 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
1/6/2010 5:19 AM | escalera
Gravatar

# 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
1/6/2010 3:39 PM | Mladen
Gravatar

# 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
3/4/2010 9:32 AM | brs
Gravatar

# 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
3/4/2010 11:11 AM | Mladen
Gravatar

# 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
8/16/2010 7:07 AM | Priyavadan joshi
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET