Updating multiple records at once.

Hello,

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#">

UPDATE SET

addedToSage = "true"

WHERE ID = #FORM.addedToSage#

</cfquery>

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

Chris
LVL 2
chrissp26Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
mkishlineCommented:
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" />
  </cfloop>
</cfquery>

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" />)
</cfquery>
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
jtreherCommented:
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>
</cfloop>

<cfif listlen(insertList) gt 0>
 <cfquery name="updateRecords" datasource="#dsl#">
   UPDATE tablename
   SET addedToSage = "true"
   WHERE ID IN (#insertlist#)
 </cfquery>
</cfif>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.