Updating multiple rows from a cfquery recordset

Question

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.

If this has to run multiple times then it might behoove you to do something about preventing recurrence of the problem first. Then you'll only have to do this once. — Dan Bracuk, Nov 15 '13 at 19:21

Matt Busche · Answer 1 · 2013-11-16T01:27:39.840

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>

edited Nov 16 '13 at 01:27

answered Nov 15 '13 at 18:58

Matt Busche

14,216
5
36
61

This looks like it will have the same value of code each time. – Dan Bracuk Nov 15 '13 at 19:16
1

Dan - Not sure why you say that. `code` and `itemID` appear to be columns in the query, so the values would change on each iteration. – Leigh Nov 15 '13 at 20:28
1

In the [original code](http://stackoverflow.com/questions/20003614/coldfusion-outofmemoryerror-cf9-wheels#comment29791546_20003614) they are function arguments, but passed in from a query. Scoping appropriately would clarify the intent and remove the need for the comments, and whilst on that subject... TSQL uses `--` for line comments; not sure that `//` is supported. – Peter Boughton Nov 16 '13 at 00:29
Ahh, you are right the original query column names are different. Definitely a good argument for scoping (no pun intended). *RE: not sure that // is supported* It is not. – Leigh Nov 16 '13 at 01:03

James A Mohler · Accepted Answer · 2013-11-15T20:01:00.040

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>

Updating multiple rows from a cfquery recordset

2 Answers2

Linked