I have these two queries below that I am trying to use in order to make an update in my db. The first query pulls all responses and there are multiple responses for each itemID. I would like to loop through all of these response and make updates to another table in the db. Any help would be appreciated!
<!--- Get misconceptions --->
<cfquery name="getMiscAnswers" datasource="#request.datasource#">
where itemID in (940,944,948,953,957)
and answerID = 'M'
I was using this below, but it isn't updating properly....
<cfquery name="updateReportAnswers" datasource="#request.datasource#">
set misconception = (CASE WHEN studentresponse = '' THEN 2 WHEN studentresponse = '#answer#' THEN 1 ELSE 0 END),
reportDisplay = (CASE WHEN studentresponse = '' THEN 2 WHEN studentresponse = '#answer#' THEN 3 when reportDisplay = 1 then 1 else 0 END)
where itemNumber = #itemID#
and itemsetID = #this.itemsetID#