Assume a table structure of MyTable(KEY, datafield1, datafield2...)
Frequently I wish to either update a current record, or place a brand new record whether it does not exist.
basically if (key is available) Run Update command ELSE run place command
What is the best carrying out method to write this?
Men, remember about transactions. Performance is nice, but simple (IF Is available..) approach is extremely harmful.
When multiple threads will attempt to do Place-or-update it is simple to get primary key breach.
Solutions supplied by @Love Crawford &lifier @Esteban show general idea but error-prone.
To prevent deadlocks and PK violations you should use something similar to this:
begin tran if is available (choose * from table with (updlock,serializable) where key = @key) begin update table set ... where key = @key finish else begin place table (key, ...) values (@key, ...) finish commit tran
begin tran update table with (serializable) set ... where kay = @key if @@rowcount = begin place table (key, ...) values (@key,..) finish commit tran
Do an UPSERT:
UPDATE MyTable SET FieldA=@FieldA WHERE Key=@Key IF @@ROWCOUNT = Place INTO MyTable (FieldA) VALUES (@FieldA)
@Love Crawford's is a great means by SQL 2005 and below, though if you are granting repetition it will visit the first guy to Therefore it. The only issue is the fact that for card inserts will still be two IO procedures.
MS Sql2008 introduces
merge in the SQL:2003 standard:
merge tablename as target using (values ('new value', 'different value')) as source (field1, field2) on target.idfield = 7 when matched up then update set field1 = source.field1, field2 = source.field2, ... if not matched up then place ( idfield, field1, field2, ... ) values ( 7, source.field1, source.field2, ... )
Now it is only one IO operation, but awful code :-(
If you wish to UPSERT several record at any given time you should use the ANSI SQL:2003 DML statement MERGE.
MERGE INTO table_title WITH (HOLDLOCK) USING table_title ON (condition) WHEN Matched up THEN UPDATE SET column1 = value1 [, column2 = value2 ...] If Not Matched up THEN Place (column1 [, column2 ...]) VALUES (value1 [, value2 ...])
Take a look at Resembling MERGE Statement in SQL Server 2005.
IF Is available (Choose * FROM [Table] WHERE ID = rowID) UPDATE [Table] SET propertyOne = propOne, property2 . . . ELSE Place INTO [Table] (propOne, propTwo . . .)
Alas, even going to my very own hindrance, I have to admit the solutions that this w/o a choose appear to become better given that they get the job done with one less step.
In SQL 2008 you should use the MERGE statement