[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

update db table with list

Posted on 2011-03-22
7
Medium Priority
?
268 Views
Last Modified: 2013-12-24
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
Comment
Question by:COwebmaster
  • 3
  • 3
7 Comments
 
LVL 53

Expert Comment

by:_agx_
ID: 35192343
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
 

Author Comment

by:COwebmaster
ID: 35192442
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
 
LVL 53

Accepted Solution

by:
_agx_ earned 1336 total points
ID: 35192478
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 

Author Comment

by:COwebmaster
ID: 35193028
yes, that's correct.  I'll give it a whirl.
0
 
LVL 29

Assisted Solution

by:Pravin Asar
Pravin Asar earned 664 total points
ID: 35402998
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
 
LVL 53

Assisted Solution

by:_agx_
_agx_ earned 1336 total points
ID: 35407953
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
 

Author Closing Comment

by:COwebmaster
ID: 35517607
Thank you!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

612 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