Solved

update db table with list

Posted on 2011-03-22
7
220 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 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

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

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

746 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

11 Experts available now in Live!

Get 1:1 Help Now