Solved

update db table with list

Posted on 2011-03-22
7
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 52

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 52

Accepted Solution

by:
_agx_ earned 334 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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 166 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 52

Assisted Solution

by:_agx_
_agx_ earned 334 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 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