Solved

update db table with list

Posted on 2011-03-22
7
259 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
Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
What You Need to Know when Searching for a Webhost Provider
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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