Ask a Grokker: How to UPDATE a stored Query object via QoQ
ColdFusion's Query of Query functionality is a pretty unique and powerful tool, once you get your head around what's going on and how it can be useful to you.
Recently, one of my colleagues asked if it was possible to use the SQL UPDATE operation to modify the contents of the in-memory result set. While the answer to that question is ultimately NO, that doesn't mean you can't accomplish the same goal another way. As the saying goes, there's more than one way to skin a cat.
Let's start by creating a query object that we'll later want to change:
//create an empty query to work with
variables.qryFoo = queryNew("a,b,c","varchar,varchar,varchar");
//add a row and fill it with some data
queryAddRow(qryFoo);
querySetCell(qryFoo,"a","aaaaaa");
querySetCell(qryFoo,"b","aaaaaa");
querySetCell(qryFoo,"c","aaaaaa");
Now, as I said, you can't just UPDATE this query object via Query of Queries. This code won't work:
UPDATE qryFoo SET b='bbbbbb', c='cccccc'
However, you can achieve the same effect with a SELECT statement:
<cfquery name="qryFoo" dbtype="query">
SELECT a, 'bbbbbb' AS b, 'cccccc' AS c
FROM qryFoo
</cfquery>
Simple, yet effective. I love it!
Update:
Per Nathan's comment, it wasn't clear that my original intention was to overwrite the query object with the QoQ select. I've updated the code above to reflect that.
By naming the QoQ the same as the original query object, the result of the select will overwrite the original data. This doesn't give you granular control to update a single record, but if you want to change the value of every row in specific column(s), then it's an easy way to go.
Of course, this begs the question: Is it possible to do a granular update using select and a union? And does QoQ even support Union?
Answer? YES!
<cfscript>
//create an empty query to work with
variables.qryFoo = queryNew("a,b,c","varchar,varchar,varchar");
//add a few rows and fill them with some data
queryAddRow(qryFoo);
querySetCell(qryFoo,"a","aaaaaa");
querySetCell(qryFoo,"b","aaaaaa");
querySetCell(qryFoo,"c","aaaaaa");
queryAddRow(qryFoo);
querySetCell(qryFoo,"a","bbbbbb");
querySetCell(qryFoo,"b","bbbbbb");
querySetCell(qryFoo,"c","bbbbbb");
queryAddRow(qryFoo);
querySetCell(qryFoo,"a","cccccc");
querySetCell(qryFoo,"b","cccccc");
querySetCell(qryFoo,"c","cccccc");
</cfscript>
<cfdump var="#qryFoo#" label="before">
<cfquery name="qryFoo" dbtype="query">
SELECT a, 'bbbbbb' as b, 'cccccc' as c
FROM qryFoo
WHERE a = 'aaaaaa'
UNION
SELECT a, b, c
FROM qryFoo
WHERE a <> 'aaaaaa'
</cfquery>
<cfdump var="#qryFoo#" label="after">
The dumps from the above code look like this:
As you can see, the code initialized 3 rows, with each record containing all A's, all B's, and all C's, and then with the QoQ, we've changed just the all A's row to have B's and C's in the B and C columns, using union.
Cool stuff.
in Ask a Grokker | ColdFusion Posted 2010-05-12 11:10
6 responses:
QuerySetCell(qryFoo,"b","bbbbbb",1)
or:
qryFoo["c"][1] = "cccccc"
Not as convenient as an UPDATE SQL statement, but it does let you update the query. (I really wish QofQ had UPDATE, INSERT and DELETE support.)
If you need powerful object querying, most ORM's have some level of object querying capabilities including update/delete. Does CF9's ORM support anything like that?
But if you're using a query metaphor to work with generic data in memory and it makes it tough or overly verbose to change/delete in memory, I wonder why use it at all over something like a generic array, a hash, or some collection of objects? Only reason I ask is that queries of queries always frustrated me for this very reason.
Leave a comment: