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.
- Check Case statement in SQL
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.
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.
SET Availability=CASE When ProductId='1' Then '5'
When ProductId='2' Then '5'
When ProductId='6' Then '10'
After executing above query,you will get below message notified that all of your rows of table are updated.
Now,you can see my all rows of ProductId 1,ProductId 2 & ProductId 5’s values are successfully changed.Check below image.
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.