Print Page | Close Window

updating multiple records

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=6657
Printed Date: 31 March 2026 at 1:22pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com


Topic: updating multiple records
Posted By: KCWebMonkey
Subject: updating multiple records
Date Posted: 23 October 2003 at 12:27pm

anyone know of a good article on an efficient way to update multiple records at a time in a db?




Replies:
Posted By: dpyers
Date Posted: 23 October 2003 at 6:03pm
Access or MS SQL?

-------------

Lead me not into temptation... I know the short cut, follow me.


Posted By: KCWebMonkey
Date Posted: 23 October 2003 at 8:12pm
access for now. but i want to know how to do this in sql db's also


Posted By: dpyers
Date Posted: 24 October 2003 at 12:39am

Can't recall seeing it done in Access, although I believe I saw something about using a data adapter with a repeater against Access in .net.

Most SQL DB's have a "ROWSET" concept that you can use either within a transaction or as simple lock and load. The big issue is figuring out how you want to handle it if one of the record updates fails but the rest go through.

I might have some UPDATE QUERY ms sql code that I used as a stored proc to do bulk changes on groups of records. Not quite the same thing, but I'll try to dig it out this weekend. I've also got something that passes an XML stream into an SQL parameter used by update query, uses openxml to turn it into a set, then processes the set; if that would be more useful to you.

BTW... The rowset stuff is pretty straight forward but uses cursors so you'll see a perfomance penalty. DBA's will declare that your mother was a hampster and your father smelt of elderberries!



-------------

Lead me not into temptation... I know the short cut, follow me.


Posted By: KCWebMonkey
Date Posted: 24 October 2003 at 6:49am

thanks for the input,

right now the db i need to do this with is in access. there has to be a way to do it. maybe i can take all of the values with their individual id numbers and loop an update using one id at a time?!?



Posted By: Bunce
Date Posted: 26 October 2003 at 6:06pm

What types of updates do you need to do?

To update the same fields in multiple records at once just add a WHERE clause to your update statement.

If you want to make *different* types of changes to multiple records then there's no way around applying a different UPDATE statement to each record.. how else would the database know what changes to make to each?

To apply multiple UPDATE statements, you can place them all in a stored proc and/or wrap them in a transaction and loop through them in your ASP code.

Cheers,
Andrew



-------------
There have been many, many posts made throughout the world...
This was one of them.



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net