As far as I am aware there are about 3 distinct ways to update multiple rows in SQL Server database with different values.
Earlier I mentioned how to insert multiple rows in a single SQL statement using row constructors. Unfortunately there is no way to do something like this pseudo code bellow for UPDATEs:
UPDATE myTable (col1,col2) SET (val1,val2) WHERE col3='val3', (val3,val4) WHERE col3='val3'
UPDATE myTable SET (col1=val1, col2=val2), (col1=val3,col2=val4) WHERE (col3='val1'),(col3='val4')
So what are the options? I picked out the following three. If you know any others please share! These methods can be used separately depending on the way you get the data for update, or can be combined inside a stored procedure.
Execute multiple UPDATE statements
This implies execution of a list of single UPDATE commands. Look at this example bellow for the main idea:
com = com & "UPDATE myTable SET col1='val1', col2='val4' WHERE col3='val7';" com = com & "UPDATE myTable SET col1='val2', col2='val5' WHERE col3='val8';" com = com & "UPDATE myTable SET col1='val3', col2='val6' WHERE col3='val9';" Execute(com)
Using CASE expression
CASE expressions were introduced in SQL-92 and are available since SQL Server 2005. Read more on CASE expressions in the MSDN article.
UPDATE myTable SET col1 = CASE col2 WHEN '1' THEN 'one' WHEN '2' THEN 'two' END
To speed things up it might be a good idea to reduce the search criteria by adding WHERE … IN … clause:
UPDATE myTable SET col1 = CASE col2 WHEN '1' THEN 'one' WHEN '2' THEN 'two' END WHERE col2 IN ('1','2',...)
To update more than one field you have to specify the list of columns just like for an common UPDATE.
UPDATE myTable SET col1 = CASE col3 WHEN '1' THEN 'one' WHEN '2' THEN 'two' END, col2 = CASE col4 WHEN '1' THEN 'one' WHEN '2' THEN 'two' END WHERE col1 IN ('1','2')
Note, all data types used in the THEN clause of CASE expressions must be compatible data types. If the data type used is not compatible then SQL Server will return an error because implicit data type conversion is not supported.
According to Execution plan query with CASE expression takes about the same time as trivial updates as it doesn’t require a full table scan and uses clustered index to locate the record.
Update one table with JOIN on another table
Using JOIN you can update one table based on the contents of another table or tables. Great news as starting SQL Server 2008 it’s possible to use new parameter type – table-valued parameters. This means that you can send multiple rows of data to a Transact-SQL statement or a routine without creating a temporary table or many parameters!
In the following example I declare in-memory data table bufTable, insert a few rows into it and update myTable with values taken from bufTable:
DECLARE @bufTable TABLE(id INT, col1 VARCHAR(30)); INSERT INTO @bufTable(id,col1)( SELECT 1, '11' UNION ALL SELECT 2, '22' UNION ALL SELECT 3, '33'); UPDATE myTable set myTable.col1 = @bufTable.col1 FROM myTable INNER JOIN @bufTable on myTable.id = @bufTable.id and @bufTable.id='5'
Still you don’t have to use table variables; you may want to create a temporary table or use an already existing regular table.
Now I don’t take into consideration the resources for temporary table or table variable to be created. According to the Execution plan this method required more I/O recourses comparing to other methods as the data has to be retrieved and joined with another table. The update action is made to the results of that join.
Note, unlike for temporary tables, statistics cannot be created against table variables. This shouldn’t be a problem though as table variables generally exist for a specific purpose and aren’t used for a wide range of ad-hoc queries.
Such trivial updates are quick and optimized. The data for update could be either a scalar data or taken from the subquery results. The only drawback of this approach is that each update is executed in its own separate transaction.
As you see there shouldn’t be any problem to choose between the 3 methods – the best solution strongly depends on the source and format you have the data in and the results you are trying to achieve.
And the last thing – once you have chosen the appropriate option, you may want to run these methods or inside a stored procedure. And you will face another problem – SQL Server doesn’t support arrays as parameters to stored procedures.
Again you have three options 🙂
- Execute stored procedure in a loop with new parameters each time
- Declare a varchar(4000) or text variable that would contain your comma separated data. Then you would have to split that list in the stored procedure. Alternative you can pass data as an XML string parameter and use OPENXML rowset provider inside of the stored procedure to select values from the provided XML. Read more about this solution in this article. Erland Sommarskog wrote about the performance of the different methods here – http://www.sommarskog.se/arrays-in-sql-perftest.html.
- If you’re using SQL Server 2008, you can enjoy the possibility to use new table-valued parameters. Table-valued parameters allow you to pass a whole table of data into a stored procedure in one go. Rather than passing XML or a string, multi-valued parameters can be represented quite naturally.