Updating multiple records at once.

Posted on 2006-05-09
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

    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" />)
    LVL 5

    Assisted Solution

    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

    Network it in WD Red

    There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

    Join & Write a Comment

    In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
    Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now