RSS

Update multiple rows with different values

18 Jun

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'  

or

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.

  1. 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)
    
  2. 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.

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

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

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 πŸ™‚

  1. Execute stored procedure in a loop with new parameters each time
  2. 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.
  3. 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.
Advertisements
 
13 Comments

Posted by on June 18, 2009 in SQL

 

Tags: , , , ,

13 responses to “Update multiple rows with different values

  1. Yogesh

    June 22, 2009 at 11:58 pm

    Really nice post Dasha. Do you have any useful links to read on stored procedures? Thanks.
    –Yo

     
  2. Zugal

    September 9, 2009 at 3:31 pm

    Help me….
    My problem is–
    TableA has four columns: a, b, c, d (a is the primary key column)
    TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)
    The foreign key relationship between the two tables is based on A.a = B.a1
    Now I want to enter in TableA,after entering is it possible to update automatically the “a1” column of TableB.As “a1″refers to “a” column of Table A.

     
  3. Asrar

    May 18, 2010 at 12:05 am

    Thanks for sharing. I used option #2 (using case expression).

     
    • Dasha

      May 18, 2010 at 7:17 pm

      Glad it helped πŸ™‚

       
  4. Adil Tata

    August 11, 2010 at 7:05 pm

    Hey there, thanks for the tip. the update syntax drove me nuts till I found your tip.

     
  5. David

    January 25, 2011 at 8:23 pm

    Hi Dasha,
    when I try to use option 2, only one row gets updated each time I execute the statement so I would need to execute it 2 times if I wanted to update 2 rows. I would think with one execution all rows should get updated.
    I am not sure what I am missing. I would appreciate any help.

     
  6. Dasha

    January 31, 2011 at 5:27 pm

    Hi David,

    Could you post the SQL you are executing?

     
  7. David

    January 31, 2011 at 6:50 pm

    Hi Dasha,
    Thanks for your respond. I tried again and this time it worked!!! I am not sure what I did wrong last time, but it is working now.
    Thanks,

     
    • Dasha

      January 31, 2011 at 8:40 pm

      Great!

       
  8. Uday Satardekar

    June 16, 2011 at 7:28 am

    sir,
    I am uday
    I am inserting multiple rows in two tables with relationship using xml.
    I trying to inserting compny id(company table) in email table using @@IDENTITY

    how i can write stored procedure for that

     
    • Dasha Salo

      June 16, 2011 at 8:02 am

      Hi Uday,

      Actually I am female πŸ˜‰

      I need more details to answer your question. Maybe you could show some code?

       
  9. Alpesh

    June 21, 2011 at 11:42 am

    Hi Dasha,

    Good post..it really helped me!!!

    Thanks for sharing!!!

     
  10. mmakoena

    July 15, 2011 at 11:28 am

    Thank you! just wwhat i needed just in time πŸ™‚ !!

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: