Updating multiple rows from a cfquery recordset

问题:

Is there a way to update multiple rows of data when the data coming in is from the result of a CFQuery? Currently this run <cfquery> multiple times. Is there a way to do it in one uqery operation?

<cfloop query=loc.fixItems>
    <cfset loc.count++>
    <cfset var categoryName = loc.fixItems.categoryName>


    <cfquery>
      update items
      set code = <cfqueryparam value="#code#">
      where id = <cfqueryparam value="#itemId#">
    </cfquery>
</cfloop>

This can run multiple times and put a heavy load on the server.

回答1:

You should be able to put your loop inside the cfquery

<cfquery>
  <cfloop query=loc.fixItems>
    <cfset loc.count++>
    <cfset var categoryName = loc.fixItems.categoryName>    
    update items
    set code = <cfqueryparam value="#code#"> -- code needs to be dynamic
    where id = <cfqueryparam value="#itemId#">; -- itemId needs to be dynamic
  </cfloop>
</cfquery>

回答2:

Load data into an XML variable and update in bulk

<cfsavecontent variables="xmlData">
<ul class="xoxo">
   <cfoutput query="loc.fixItems">
     <li><b>#xmlformat(id)#</b> <code>#code#</code></li>
   </cfoutput>  
 </ul>
 </cfsavecontent>

 <!---
 <cfoutput>#xmlData#</cfoutput>
 --->

 <cfquery>
 DECLARE @xmlData xml = <cfqueryparam cfsqltype="CF_SQL_varchar" value="#xmlData#">


 ;
 WITH Data (id, code) AS (
    SELECT  tbl.Col.value('b[1]','varchar(20)') AS ID,
    tbl.Col.value('code[1]','varchar(50)') AS Code
    FROM    @xmlData.nodes('/ul/li') tbl(Col)
    )

 UPDATE items
   SET items.code = Data.code
 FROM items
 INNER JOIN Data
   ON items.id = Data.id    
 </cfquery>