Delete from a Query of Queries

I recently had a problem where I wanted to delete records from a ColdFusion recordset. I often use build query objects on the fly to store data. Unfortunately you can’t use delete or update statements in Query of Queries, in the end I used a select statement with a where clause to filter out the unwanted records which works a treat!

Example:

<!--- populate a query object --->
<cfset qryExample = QueryNew('id') />

<cfloop from="1" to="10" index="ndx">
  <cfset QueryAddRow(qryExample) />
  <cfset QuerySetCell(qryExample, "id", ndx) />
</cfloop>

<cfdump var="#qryExample#" /><!--- remove a record from our recordset using Query of Queries --->
<cfquery name="qryExample" dbtype="query">
  select * from qryExample
  where id <> 1
</cfquery>
<cfdump var="#qryExample#" />