Update multiple rows in SQL Server with different values

12 Flares Filament.io 12 Flares ×

In this SQL tutorial,I will explain about How to update multiple rows in SQL Server with single SQL query? Before you read this article.You should have knowledge of Case statement.I wrote about How to use Case statement in my previous post.

If you also want to make tutorial on your localhost,You can get create & insert table script from above link article.I assume that you had create table & insert that data.Now on select query,I get these rows as shown below image.
SQL-Server-Product-Table

Now,as discussed earlier post,I have product’s quantity=0 for ProductId 2 & ProductId 6.Now as I want to update both of the rows.I have to make queries differently for that.But if you have knowledge of Case Statement,You can easily update both of the rows.

I will update Availability column for ProductId 1’s value from 2 to 5 & ProductId 2’s value  from 0 to 5 & ProductId 6’s value from 0 to 10.

Here you don’t have to give old value as we do in Update query.Because we do update all rows here.

Here’s query to update multiple rows in SQL Server.

After executing above query,you will get below message notified that all of your rows of table are updated.

SQL-server-Message-Update-Muliple-rows-Message

Now,you can see my all rows of ProductId 1,ProductId 2 & ProductId 5’s values are successfully changed.Check below image.

SQL-server-Updated-Muliple-rows-Product-table

Make sure that you don’t forget to write Else statement with your column name,otherwise it will insert NULL values for all other rows.

Also you have to keep in mind that when you have large no. of data in your table,you should not use this method,because it updates all rows which is quite costly.

Check all SQL Tutorial If you have any problems,queries,don’t forget to comment below.

One thought on “Update multiple rows in SQL Server with different values

  1. Ibukunoluwa Ogunyinka says:

    Thanks for the pointer, it was very helpful on one of my projects.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">