Updating multiple records at once.

Posted on 2006-05-09
Medium Priority
Last Modified: 2013-12-24

I have a number of checkboxes on a field all have the same name: addedToSage. When multiple checkboxes are selected the output appears like this:

1,2,3,4,5,6,7,8,9,10 etc etc

Those are the ID's of the records I have outputted on the page.

What i'm trying to do is update each record by the ID listed in the outputted variable when someone hits submit.

So essentially what I need to do is this:

<cfquery name="updateRecords" dataSource="#dsl#">


addedToSage = "true"

WHERE ID = #FORM.addedToSage#


This doesn't work. So is it a case of using listLen and establishing the length of the addedToSage string and then using a loop to update each record in turn?

Thanks for you time

Question by:chrissp26
LVL 12

Accepted Solution

mkishline earned 1000 total points
ID: 16639728
I believe what you're looking for is something like this.

<cfquery name="updateRecords" datasource="#dsl#">
  UPDATE tablename
  SET addedToSage = "true"
  WHERE ID = <cfqueryparam value="#ListFirst(form.addedToSage)#" CFSQLType="CF_SQL_INTEGER" />
  <cfloop index="i" list="#ListRest(form.addedToSage)#">
    OR ID = <cfqueryparam value="#i#" CFSQLType="CF_SQL_INTEGER" />

some people recommend the following (but I've had bad luck trying to get it to work)
<cfquery name="updateRecords" datasource="#dsl#">
  UPDATE tablename
  SET addedToSage = "true"
  WHERE ID IN (<cfqueryparam value="#form.addedToSage#" CFSQLType="CF_SQL_VARCHAR" />)

Assisted Solution

jtreher earned 1000 total points
ID: 16642616
I believe that you could just loop through. This would require id to be a numeric datatype. I'm simply adding some validation to ensure that your data is correct to save some headaches.

<cfset insertList = "">

<cfloop list="#form.addedToStage#" index="x">
 <cfif isnumeric(x)>
  <cfset insertList = listappend(insertList,x)>

<cfif listlen(insertList) gt 0>
 <cfquery name="updateRecords" datasource="#dsl#">
   UPDATE tablename
   SET addedToSage = "true"
   WHERE ID IN (#insertlist#)

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month15 days, 3 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question