• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1053
  • Last Modified:

Update sql table using coldfusion query and loop

I have a table that I need to clear values first then update it with correct data. It needs to happen 3 times. For some reason it's only happening once.
Please see code below:

<cfquery name="updateFeaturedCourse" datasource="#application.dsName#" username="#application.dsUserName#" password="#application.dsPassword#">
                        UPDATE courses
                        SET featuredcourse = 'N', featuredcourseordernumber = 0
                        WHERE featuredcourse = 'Y'
                <cfloop from="1" to="3" index="ThisRow">
                        <!--- extract each value --->
                  UPDATE courses
                  SET featuredcourse = 'Y', featuredcourseordernumber = '#Evaluate('form.hcf_#ThisRow#')#'
                  WHERE courseid ='#Evaluate('form.hf_#ThisRow#')#'
                        </cfloop>
            </cfquery>
                  <cflocation url="set_featured_courses.cfm?aid=m" addtoken="no">
0
asaworker
Asked:
asaworker
1 Solution
 
rob_lorentzCommented:
as a point of style i try to limit myself to 1 SQL statement per cfquery

So, first of all i would split your 2 updates into 2 cfqueries.

                        <!--- extract each value --->

<cfquery >
    UPDATE courses
    SET featuredcourse = 'N', featuredcourseordernumber = 0
    WHERE featuredcourse = 'Y'
</cfquery>
<cfloop from="1" to="3" index="ThisRow">
    <cfquery>
        UPDATE courses
        SET featuredcourse = 'Y', featuredcourseordernumber = '#Evaluate('form.hcf_#ThisRow#')#'
         WHERE courseid ='#Evaluate('form.hf_#ThisRow#')#'
    </cfquery>
</cfloop>

0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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