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:

dump results for QoQ with union

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.

Posted in Ask a Grokker | ColdFusion | 6 Responses

6 responses:

Nathan Mische May 12, 2010 at 1:20 PM Well, that doesn't really update the query. To update the query you could use QuerySetCell, or use array syntax to access the column and row you want to update. So you could do something like:

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.)
Adam May 12, 2010 at 2:00 PM Nathan, my bad. I meant to more explicitly state that the QoQ should overwrite the original query object in memory. I've updated the post with that detail, as well as some other thoughts on using UNION (that you and I discussed offline).
Dave Konopka May 12, 2010 at 2:16 PM I never found the query metaphor very useful for working with dynamic data for this very reason. More often than not it felt like a glorified looping mechanism.

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?
Adam May 12, 2010 at 2:24 PM Dave, I don't follow. ORM is for persistent data -- you could just run the updates (via ORM or otherwise) against the database. Whereas the methods I outline here might be useful if you're getting data back from an external source you can't control (perhaps from a webservice) and you want to massage the data before displaying or otherwise using it. What am I missing?
Dave Konopka May 12, 2010 at 2:37 PM I suppose I didn't follow your examples, at least not the source of the data. So yeah, an ORM is for persisted data. Once that data is in object form though most ORM frameworks provide querying languages to interact with the objects. You can use that to interact with the objects regardless of whether you persist changes back to the database or not. You're right, that's not much help if your data isn't coming from a database.

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.
Adam May 12, 2010 at 2:44 PM I see. I never found them frustrating (at least not for this reason). I think it originally came up because when people think about queries, they think about SQL; so when they want to update a query object in memory, their first instinct might be to use SQL. I never had any real frustrations just using the functions that Nathan mentioned, though.

fusiongrokker