This never occurred to me to try, but today, trying to solve a problem, I ran a SQL statement that attempted to use the UPDATE clause on an in-memory table. Much to my surprise (and delight), this worked! I had no idea that you could do this. To test it even further, I ran a DELETE clause as well. Worked like a charm!
Here is a demo of this in action:
<cfquery name="qID" datasource="#REQUEST.DSN.Source#"> DECLARE @id TABLE ( id1 INT, id2 INT ) ; <!--- Populate the in-memory table variable with the pivot data. We are going to store the same ID into both columns so that we can demonstrate that it was updated. ---> INSERT INTO @id ( id1, id2 )( SELECT TOP 5 p.id AS id1, p.id AS id2 FROM pivot100 p ); <!--- Update the in-memory query. ---> UPDATE @id SET id2 = (id2 + 5) ; <!--- Delete from the in-memory query. ---> DELETE FROM @id WHERE id1 >= 4 ; <!--- Select all values from the in-memory table. ---> SELECT * FROM @id </cfquery> <!--- Dump out the query with both ID columns. ---> <cfdump var="#qID#" label="ID Query" />
CFDumping out the returned ColdFusion query object, we get:
Notice that the UPDATE added 5 to the id2 column and that the DELETE deleted the last two records. I can't believe I am only learning this now!
Want to use code from this post? Check out the license.