Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

update db table with list

I have the following query and am trying to loop through this list and update the column with each value but it's not working.  So each value in the list needs to be updated in each row in the table.  Any ideas what I'm doing wrong?

<cfset newUrlList = '1,2,3,4,5">

<cfloop list="#newUrlList#" index="curLine">
<cfquery name="updmarker" datasource="dsn">
update table
set marker = '#curLine#'
</cfquery>
</cfloop>
0
COwebmaster
Asked:
COwebmaster
  • 3
  • 3
3 Solutions
 
_agx_Commented:
Looks like you're missing a WHERE clause that says which records to update.  

As it is now, you're updating all records in the table every time you loop. Each loop overwrites the previous value. So the first time all records are set to "1"

      update table set marker = '1'  <!--- #curLine# = 1 --->

On the next loop they're set to "2", etc...

      update table set marker = '2'   <!--- #curLine# = 2 --->

After the final loop all records will be set to "5".  

You need to add a WHERE clause so the query knows which records to update
0
 
COwebmasterAuthor Commented:
ok, so like this?

<cfset PKIDs = '10,20,30,40,50">
<cfset newUrlList = '1,2,3,4,5">

<cfloop list="#newUrlList#" index="curLine">
<cfquery name="updmarker" datasource="dsn">
update table
set marker = '#curLine#'
where markerID ='#PKIDs#'
</cfquery>
</cfloop>
0
 
_agx_Commented:
No, you'd need a from/to loop.  

<cfset PKIDs = '10,20,30,40,50">
<cfset newUrlList = '1,2,3,4,5">
<cfloop from="1" to="#listLen(newUrlList)#" index="x">
     <cfset thePK   = listGetAt(PKIDs, x)>
     <cfset theURL = listGetAt(newUrlList , x)>
    <cfquery name="updmarker" datasource="dsn">
      update table
      set marker = '#theURL#'
      where markerID ='#thePK#'
    </cfquery>
</cfloop>

Be sure to validate the two lists have the same length. Otherwise the script may error out.  Also, both variables contain numbers only right (not text)?

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
COwebmasterAuthor Commented:
yes, that's correct.  I'll give it a whirl.
0
 
Pravin AsarCommented:
With cfquery use

cfqueryparam.

Also to be safe, I would use trim to get rid of leading and trailing white spaces.


<cfset PreviousIDs = '10,20,30,40,50">
<cfset newUrlList = '1,2,3,4,5">

<cfloop list="#newUrlList#" index="curLine">
<cfquery name="updmarker" datasource="dsn">
      update table
      set marker = <cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#trim(curline)#" null="false" />
      where markerID = #curline#;
</cfquery>
</cfloop>
0
 
_agx_Commented:
Agreed about trimming and cfqueryparam.  Especially for queries w/in a loop.  I didn't bother mentioning cfqueryparam this time because I'm sure COwebmaster's heard it from me before ;-)

Though it should be used on all query params.  From the single quotes in the original sql I'm guessing the fields are varchar's? So the type would probably be cf_sql_varchar rather than cf_sql_longvarchar.  But that's just a guess.

...
  <cfquery name="updmarker" datasource="dsn">
      update table
      set marker = <cfqueryparam value="#theURL#" cfsqltype="cf_sql_varchar">
      where markerID = <cfqueryparam value="#thePK#" cfsqltype="cf_sql_varchar">
    </cfquery>
..

Open in new window

0
 
COwebmasterAuthor Commented:
Thank you!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now