Upate Multiple Rows using update query in sql
-
Hello everyone, I query the db and I get a number of rows returned. They are displayed in a form with fields to be updated. I know how to do the update on a one by one basis. What I want to do is be able to update multiple rows at once. Let say for example 5 fields per row and 10 rows. There is something about arrays and loop thru it that I cannot get to work. Any help would be greatly appreciated. Thanks Murali
-
Hello everyone, I query the db and I get a number of rows returned. They are displayed in a form with fields to be updated. I know how to do the update on a one by one basis. What I want to do is be able to update multiple rows at once. Let say for example 5 fields per row and 10 rows. There is something about arrays and loop thru it that I cannot get to work. Any help would be greatly appreciated. Thanks Murali
I generally loop through a datatable and update each row individually, there are some articles around where an xml string is used to pass a collection of rows into the database for updating but I have never actually needed to use it.
Never underestimate the power of human stupidity RAH
-
Hello everyone, I query the db and I get a number of rows returned. They are displayed in a form with fields to be updated. I know how to do the update on a one by one basis. What I want to do is be able to update multiple rows at once. Let say for example 5 fields per row and 10 rows. There is something about arrays and loop thru it that I cannot get to work. Any help would be greatly appreciated. Thanks Murali
If the new values can be expressed by expressions, then you can use an update query to update multiple records and multiple fields. For example,
Update MyTable Set Price=Price*1.05, ItemVolume=ItemVolume+100 Where Category='Electronics';
Hope this helps. Happy programming!
-
Hello everyone, I query the db and I get a number of rows returned. They are displayed in a form with fields to be updated. I know how to do the update on a one by one basis. What I want to do is be able to update multiple rows at once. Let say for example 5 fields per row and 10 rows. There is something about arrays and loop thru it that I cannot get to work. Any help would be greatly appreciated. Thanks Murali
What language are you using for your front end? Some languages will support this. For example, Java supports batch updates so you can loop through all the entries setting the update parameters for each in turn, then do an executeBatch to do the lot in one go. (OK, before anyone points this out, not all JDBC drivers support this.) C# has something similar, although I've never used it myself. And if you can do it in C#, you can probably do it in VB.NET as well.
-
What language are you using for your front end? Some languages will support this. For example, Java supports batch updates so you can loop through all the entries setting the update parameters for each in turn, then do an executeBatch to do the lot in one go. (OK, before anyone points this out, not all JDBC drivers support this.) C# has something similar, although I've never used it myself. And if you can do it in C#, you can probably do it in VB.NET as well.
If using T-SQL you can use a join to populate the table with the information.
-
Hello everyone, I query the db and I get a number of rows returned. They are displayed in a form with fields to be updated. I know how to do the update on a one by one basis. What I want to do is be able to update multiple rows at once. Let say for example 5 fields per row and 10 rows. There is something about arrays and loop thru it that I cannot get to work. Any help would be greatly appreciated. Thanks Murali
If you are using SQL 2008 you can use Table Value Parameters to acheive this: Table-Value Parameters in SQL Server 2008 - VB.NET[^] :-\
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife