Updating a row in sql
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.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). one suggested writing to temp table then re-insert to original table.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.
This is faster in this scenario because the vast majority of the time it is just a single simple update.
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. As i said earlier there are two methods of doing this: 1. 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 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. IX Lock (Intent Exclusive Lock) and X Lock(Exclusive Lock). If row exists update else insert First let's create our test table: When a row doesn't exist in a table we have to insert it.