Update SQL Wikipedia.An SQLUPDATE statement changes the data of one or more records in a table.Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form 1UPDATEtablename.SETcolumnname value, columnname value.WHEREconditionFor the UPDATE to be successful, the user must have data manipulation privileges UPDATE privilege on the table or column and the updated value must not conflict with all the applicable constraints such as primary keys, unique indexes, CHECK constraints, and NOT NULL constraints.I have a SQL server table in which there are 2 columns that I want to update either of their values according to a flag sent to the stored procedure along with the.I am wondering if this is possible at all.I want to update column x if a condition is true, otherwise column y would be updated UPDATE table SET CASE CONDITION.This SQL tutorial explains how to use the SQL IN condition with syntax and examples.The SQL IN condition sometimes called the IN operator allows you to easily test.Handlers/DownloadFile.ashx?File=5c130531-454e-4c77-bf7a-830f25c4d894.png' alt='Sql If Condition In Update' title='Sql If Condition In Update' />In some databases, such as Postgre.SQL, when a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the fromlist, and each output row of the join represents an update operation for the target table.When using FROM, one should ensure that the join produces at most one output row for each row to be modified.In other words, a target row shouldnt join to more than one row from the other tables.If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.Because of this indeterminacy, referencing other tables only within sub selects is safer, though often harder to read and slower than using a join.My. SQL does not conform to ANSI standard.ExampleseditSet the value of column C1 in table T to 1, only in those rows where the value of column C2 is a.UPDATETSETC11. WHEREC2aIn table T, set the value of column C1 to 9 and the value of C3 to 4 for all rows for which the value of column C2 is a.UPDATETSETC19,C34.WHEREC2aIncrease value of column C1 by 1 if the value in column C2 is a.UPDATETSETC1C11. WHEREC2aPrepend the value in column C1 with the string text if the value in column C2 is a.UPDATETSETC1textC1.WHEREC2aSet the value of column C1 in table T1 to 2, only if the value of column C2 is found in the sublist of values in column C3 in table T2 having the column C4 equal to 0.UPDATET1. SETC12. Best Antivirus Software For Small Businesses more. WHEREC2. INSELECTC3.FROMT2. WHEREC40One may also update multiple columns in a single update statement Complex conditions and JOINs are also possible UPDATETSETA1.WHEREC11. ANDC22.Some databases allow the non standard use of the FROM clause UPDATEa.SETa. updatedcolumnupdatevalue.FROMarticlesa. JOINclassificationc.ONa. article. IDc.IDWHEREc. class. ID1.Or on Oracle systems assuming there is an index on classification.ID UPDATESELECTFROMarticles.JOINclassification.ONarticles. article.IDclassification.IDWHEREclassification.ID1SETupdatedcolumnupdatevalue.Potential issueseditSee Halloween Problem.It is possible for certain kinds of UPDATE statements to become an infinite loop when the WHERE clause and one or more SET clauses may utilize an intertwined index.Forgetting to specify the WHERE clause leads for all records to be affected by the update.Referencesedit. Conditional INSERTUPDATE Race Condition.Conditional INSERTUPDATE Race Condition.I often see conditional INSERTUPDATE code like CREATEPROCEDURE dbo.InsertOrUpdateFoo ID int, Bar int.ASSET NOCOUNT ONIFEXISTSSELECTROM dbo.Foo WHERE ID IDBEGIN UPDATE dbo.Foo SET bar bar WHERE ID IDENDELSEBEGIN INSERTINTO dbo.Foo ID, Bar VALUESID, BarENDRETURNERRORDespite its prevalence, this UPSERT code is fundamentally flawed because it assumes only a single user will execute the proc at a time.A primary key violation error can occur when executed simultaneously on different connections with the same ID value instead of the intended UPDATE.An even worse situation is when a surrogate key e.IDENTITY is used as the primary key and the conditional insert is based on a natural key that has no unique constraint or unique index.In that case, the both INSERTs will succeed but duplicate data same natural key will be inserted.A unique constraint or unique index should of course be specified on every key but thats sometimes overlooked.You can test the concurrency problem yourself by creating the following table for use with the above stored procedure on a multi processor box CREATETABLE dbo.Foo ID int. NOTNULL CONSTRAINT PKFoo PRIMARYKEY, Bar int.NOTNULLThen run the following script on different database connections after changing the WAITFOR TIME to the near future WAITFOR TIME 0.EXEC dbo. InsertOrUpdateFoo ID 1, Bar 1.I ran this test and got a primary key violation because parallel execution of the IF EXISTS test returned false on both connections so the INSERT was attempted on both.Fortunately, theres a simple solution to prevent the error 1 add an explicit transaction and 2 specify SELECT locking hints.Below is a modified version of the original stored procedure with these changes CREATEPROCEDURE dbo.InsertOrUpdateFoo ID int, Bar int.ASSET NOCOUNT, XACTABORT ONBEGINTRANIFEXISTSSELECTROM dbo.Foo WITHUPDLOCK,HOLDLOCKWHERE ID IDBEGIN UPDATE dbo.Foo SET bar bar WHERE ID IDENDELSEBEGIN INSERTINTO dbo.Foo ID, Bar VALUESID, BarENDCOMMITRETURNERRORThe UPDLOCK hint instructs SQL Server to use an update lock instead of the shared lock that would normally be acquired for the SELECT.HOLDLOCK is needed in the default READ COMMITTED isolation level to ensure that the lock is held until the end of the transaction.This more restrictive update lock will prevent simultaneous queries from either selecting or changing the locked resource.If the row exists during the SELECT, the locked resource is the existing row.If no row exists with the specified key, a range lock on the primary key is acquired to prevent inserts of the same key until the lock is released.Although not actually required, I also added SET XACTABORT ON to help ensure the explicit transaction is closed following a timeout or unexpected error.See Use Caution with Explicit Transactions in Stored Procedures to see why I recommend this as a standard practice.I should add that this explicit transaction and locking hints technique will prevent the above mentioned problems but it doesnt technically prevent a race condition.The issue with any UPSERTMERGE technique is that the last one in the race wins.If you run the above proc simultaneously on different connections with the same ID value but different Bar values, the last Bar value UPDATE will of course overwrite any previous Bar value.The issue doesnt apply only to IF statements.Even intra query subqueries like the example below can fall victim to the issue.Just like IF EXISTS, there is nothing to prevent the same concurrency problem when the NOT EXISTS predicate is evaluated simultaneously on different connections.CREATEPROCEDURE dbo.InsertFoo ID int, Bar int.ASSET NOCOUNT ONINSERTINTO dbo.Foo ID, BarVALUESID, BarWHERENOTEXISTS SELECT FROM dbo.Foo WHERE ID ID RETURNERRORBelow is the modified proc with the transaction and locking hint changes CREATEPROCEDURE dbo.InsertFoo ID int, Bar int.ASSET NOCOUNT, XACTABORT ONBEGINTRANINSERTINTO dbo.Foo ID, BarVALUESID, BarWHERENOTEXISTS SELECT FROM dbo.Foo WITHUPDLOCK,HOLDLOCK WHERE ID ID COMMITRETURNERRORIm not certain if the SQL 2.MERGE statement suffers from the same concurrency issues since I dont currently have an adequate multi processor SQL 2.I plan to install the next SQL 2.CTP on a real not virtual machine and test the proc below.Ill post the results.Sunday, October 2.
0 Comments
Leave a Reply. |