本文介绍了从cfquery记录集更新多个行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
有一种方法来更新多行数据,当进来的数据是从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记录集更新多个行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!