从cfquery记录集更新多个行 [英] Updating multiple rows from a cfquery recordset

问题描述

有一种方法来更新多行数据，当进来的数据是从CFQuery的结果？目前，这次运行< cfquery> 多次。在一个uqery操作中有办法吗？

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.

推荐答案

将数据加载到XML变量中并批量更新

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>

这篇关于从cfquery记录集更新多个行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！