Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2009 (Lansdowne, VA) with:

Running UPDATE And DELETE SQL Statements Using ColdFusion Query Of Queries

By Ben Nadel on
Tags: ColdFusion

This blog post is a proof of concept that was inspired by an "Ask Ben" question. Someone contacted me and asked me how to update the columns in a query that were left blank during the manual creation of a query using ColdFusion's QueryNew(), QueryAddRow(), QueryAddColumn() methods. I started to answer this person, explaining to them that they had to manually loop over the records to update each row explicitly because ColdFusion doesn't support UPDATE statements in ColdFusion query of queries; but then I stopped and thought about what UPDATE and DELETE SQL statements really are?

With all of its shortcomings, ColdFusion's Query of Queries functionality is still rather mind blowing; you might only be able to run SELECT statements, but the parsing that it does on the SQL and the speed with which it is executed is very impressive. As such, I wondered if there was a way to create UPDATE and DELETE statements using a SELECT statement so that we could leverage the goodness that is already baked into query of queries.

For a DELETE statement, this is actually rather easy - a DELETE statement is a SELECT statement where all of the WHERE conditions do NOT match. For example, deleting all records where a flag is zero is the same thing as selecting all records where a flag is NOT zero. UPDATE statements are a bit harder; an UPDATE statement is a SELECT statement that retrieves a sub-set of records that get updated and then merged back into the original query.

Taking this SELECT-based mind set, I wanted to see if I could create a ColdFusion custom tag that would allow developers to run UPDATE and DELETE SQL statements by transparently leveraging the existing SELECT power of ColdFusion query of queries. The following custom tag - QoQ.cfm - does not support the full gamet of query of query syntax (ex. CFQueryParam, JOINs), but, I think it very interesting.

Before we get into the custom tag itself, however, let's take a look at how it might be used. In the following code samples, we are going to manually construct a ColdFusion query and then run several UPDATE and DELETE statements on it.

First, let's build the query:

  • <!--- Create the query object. --->
  • <cfset girls = queryNew( "" ) />
  •  
  • <!--- Add the name column. --->
  • <cfset queryAddColumn(
  • girls,
  • "name",
  • "cf_sql_varchar",
  • listToArray( "Sarah,Libby,Jill,Kim" )
  • ) />
  •  
  • <!---
  • Add the "is cute" column. When we add this column, let's
  • not supply any default values. This will put NULL value /
  • empty strings in the cells.
  • --->
  • <cfset queryAddColumn(
  • girls,
  • "is_cute",
  • "cf_sql_integer",
  • arrayNew( 1 )
  • ) />
  •  
  • <!--- Output the query as it stands now. --->
  • <cfdump
  • var="#girls#"
  • label="Girls"
  • />

Notice that when we created the is_cute column, we did not provide any default values. As such, that column should contain NULL values:

 
 
 
 
 
 
Manually Creating A Query Object In ColdFusion. 
 
 
 

Now, let's update the query, using ColdFusion query of queries, setting the is_cute column to a default value of "0" where ever it is currently NULL:

  • <!---
  • Now, run a query of queries that updates the girls table,
  • setting the is_cute value to zero anywhere it is NULL.
  • NOTE: We have to use NULL here, rather than empty string
  • because the query has not been touched yet.
  • --->
  • <cf_qoq>
  • UPDATE
  • girls
  • SET
  • is_cute = 0
  • WHERE
  • is_cute IS NULL
  • </cf_qoq>
  •  
  • <!--- Output the query as it stands now. --->
  • <cfdump
  • var="#girls#"
  • label="Girls"
  • />

While a NULL value might look like an empty string when we output it, at this point, the is_cute column still contains Java NULLs. This is why we need to use the IS NULL condition, rather than the (= '') condition. After we run this update, however, the is_cute column is now populated with 0:

 
 
 
 
 
 
Running UPDATE Statements In ColdFusion Query Of Queries. 
 
 
 

Now that we have our is_cute column updated to a default of zero, let's further update its accuracy based on the names of the girls. We all know that girls named "Jill" and girls who's name starts with "K" are generally very cute, so let's update their is_cute flag to be 1:

  • <!---
  • We know for a fact that some of these girls are cute based on
  • their name. Set the is_cute flag to 1 if the name is Libby or
  • the name starts with K. Maybe I have a K-fetish and I just
  • assume those girls are cute.
  • NOTE: While I don't need the (is_cute = 0) condition at this
  • point because I know they should all be zero; however, I
  • wanted to demonstrate the compount nature of the WHERE clause
  • is still availlabe in the query of queries.
  • --->
  • <cf_qoq>
  • UPDATE
  • girls
  • SET
  • is_cute = 1
  • WHERE
  • is_cute = 0
  • AND
  • (
  • name = 'Libby'
  • OR
  • name LIKE 'K%'
  • )
  • </cf_qoq>
  •  
  • <!--- Output the query as it stands now. --->
  • <cfdump
  • var="#girls#"
  • label="Girls"
  • />

While I know that the is_cute column does contain all zero values, I wanted to put the zero-check in there simply to demonstrate the compound power of the underlying SELECT statement. When we run this query, the appropriate records have been flagged as cute:

 
 
 
 
 
 
Running UPDATE Statements In ColdFusion Query Of Queries. 
 
 
 

Now that we have the cute flags set based on the name, let's now update the names of all the cute girls to more accurately reflect their physical spectacularity. In the following update, we are going to build a nickname for each cute girl based on their current name:

  • <!---
  • Now that we have the is_cute columns updated based on fact,
  • let's go ahead and update the names of the girls to reflect
  • their cuteness. To do so, we are going to wrap the name
  • column with "Hottest" and "!". This is to demonstrate that
  • calculated columns are available in this version of query
  • of queries.
  • --->
  • <cf_qoq>
  • UPDATE
  • girls
  • SET
  • name = ('Hottest ' + name + '!')
  • WHERE
  • is_cute != 0
  • </cf_qoq>
  •  
  • <!--- Output the query as it stands now. --->
  • <cfdump
  • var="#girls#"
  • label="Girls"
  • />

As you can see, we build the nickname by wrapping the current name of the cute girl with "Hottest" and "!". I wanted to demonstrate that the SET construct here could leverage all of the calculation features of a standard query of queries. And, when we run this UPDATE, we get the following:

 
 
 
 
 
 
Running UPDATE Statements In ColdFusion Query Of Queries. 
 
 
 

Now that we've run a whole bunch of UPDATE statement, let's run a DELETE statement to trim our query a bit. In the following DELETE statement, we are going to get rid of any girls who have been deemed not cute or who's name contains "kim":

  • <!---
  • Now that we have updated our query several times, let's
  • delete some records. Delete the records where the is_cute
  • flag is zero or the name contains "Kim".
  • NOTE: The CAST() below is completely unnecesary - I just
  • wanted to demonstrate that the full range of query of
  • query constructs could be used in the WHERE clause.
  • --->
  • <cf_qoq>
  • DELETE FROM
  • girls
  • WHERE
  • CAST( is_cute AS VARCHAR ) = CAST( 0 AS VARCHAR )
  • OR
  • name LIKE '%Kim%'
  • </cf_qoq>
  •  
  • <!--- Output the query as it stands now. --->
  • <cfdump
  • var="#girls#"
  • label="Girls"
  • />

The CAST() in this DELETE statement is completely unnecessary; but, again, I just wanted to demonstrate that the WHERE clause supports the full range of query of query syntax. When we run this DELETE, we get the following result:

 
 
 
 
 
 
Running DELETE Statements In ColdFusion Query Of Queries. 
 
 
 

UPDATE and DELETE statements are not something that I need all the time; but, there are definitely time when they would be awesome to have available in ColdFusion query of queries. Before I show you the code behind this ColdFusion custom tag, let me stress again that this is a proof of concept. As such, the entire functionality of query of queries is not supported. However, if people where to show some interest in this, I am sure it is something I could build out.

QoQ.cfm

  • <!--- Check to see which mode we are executing in. --->
  • <cfif (thistag.executionMode eq "start")>
  •  
  • <!--- Nothing to do in the start mode. --->
  •  
  • <cfelse>
  •  
  • <!---
  • Now that the tag has executed, the generated content
  • should store our query of queries SQL statement. Let's
  • gather that into a variable.
  • --->
  • <cfset sqlStatement = trim( thistag.generatedContent ) />
  •  
  • <!---
  • Create a pattern class that will be used below to compile
  • regular expression patterns.
  • --->
  • <cfset patternClass = createObject(
  • "java",
  • "java.util.regex.Pattern"
  • ) />
  •  
  •  
  • <!---
  • Now that we have our SQL statement, let's check to see
  • what type of action we are performting. UPDATE and DELETE
  • will be handled custom; but, SELECT statements will just
  • be passed to the normal query of query functionality.
  • --->
  • <cfif reFind( "(?i)^[(\s]*SELECT", sqlStatement )>
  •  
  •  
  • <!---
  • When SELECTing values, the user need to supply a name
  • for the query. (NOTE: The UPDATE and DELETE) queries
  • will grab the variable out of the SQL statement).
  • --->
  • <cfparam
  • name="attributes.name"
  • type="variablename"
  • />
  •  
  • <!---
  • This is a standard SELECT statement, so just throw
  • this in a CFQuery query of queries action. Store it
  • directly into the caller-scoped variable provided
  • by the user.
  • --->
  •  
  • <!---
  • Because the reference to the original table is
  • changing to the context of this tag, we need to
  • append the caller scope to our internal variables
  • scope.
  •  
  • NOTE: This is a wicked LAME hack - this wasn't
  • really designed to be used for SELECT statements.
  • --->
  • <cfset structAppend( variables, caller ) />
  •  
  • <!--- Execute standard query of queries. --->
  • <cfquery name="caller.#attributes.name#" dbtype="query">
  • #preserveSingleQuotes( sqlStatement )#
  • </cfquery>
  •  
  •  
  • <cfelseif reFind( "(?i)^UPDATE", sqlStatement )>
  •  
  •  
  • <!---
  • The user wants to perform an UPDATE statement. Let's
  • create a regular expression pattern than will help us
  • grab the necessary parts of the query.
  • --->
  • <cfsavecontent variable="regexPattern"
  • >(?xi)
  • # The update statement. We don't need to capture this
  • # since we know what kind of statement we are doing.
  •  
  • ^UPDATE \s+
  •  
  • # The name of the table (variable) that we are going
  • # to be updating.
  •  
  • ([\w_.]+) \s+
  •  
  • # SET keyword. No need to capture this.
  •  
  • SET \s+
  •  
  • # Now, we need to capture the set of update requests.
  • # We will use this later to update the query object
  • # manually.
  •  
  • (
  • # There must be at least one set statement.
  • (?:
  • (?!WHERE)[^=]+ \s* = \s*
  • (?:
  • (?!(?:WHERE|,))
  • (?:
  • [^']
  • |
  • '[^']*(?:''[^']*)*'
  • )
  • )+
  • ,? \s*
  • )+
  • )
  •  
  • # The entire WHERE clause is optional.
  •  
  • (?:
  • # WHERE keyword. No need to capture this.
  •  
  • WHERE \s+
  •  
  • # Now, we need to capture the set of conditions.
  • # We will later use this to update the query
  • # object manually.
  •  
  • ( [\w\W]+ )
  • )?
  • </cfsavecontent>
  •  
  • <!--- Compile the pattern. --->
  • <cfset pattern = patternClass.compile(
  • javaCast( "string", regexPattern )
  • ) />
  •  
  • <!---
  • Get the matcher for the pattern using the sql
  • statement. This will give us access to the
  • captured groups.
  • --->
  • <cfset matcher = pattern.matcher(
  • javaCast( "string", sqlStatement )
  • ) />
  •  
  • <!---
  • Check to see if the pattern can be found in the
  • user-provided SQL statement.
  • --->
  • <cfif matcher.find()>
  •  
  • <!--- Get the SQL parts. --->
  • <cfset sqlParts = {
  • table = matcher.group( javaCast( "int", 1 ) ),
  • set = matcher.group( javaCast( "int", 2 ) ),
  • where = matcher.group( javaCast( "int", 3 ) )
  • } />
  •  
  • <!--- Let's extract the SET conditions. --->
  • <cfset setConditions = reMatch(
  • "[\w_]+\s*=\s*('[^']*(''[^']*)*'|[^,']+)+",
  • trim( sqlParts.set )
  • )/>
  •  
  •  
  • <!---
  • Now that we have the SET conditions, let's further
  • parse them into a collectoin of set conditions that
  • is keyed by column name. This will help use later
  • when we need to output the column list.
  • --->
  • <cfset setCollection = {} />
  •  
  • <!---
  • Loop over the set condtions to break them apart,
  • placing the clean column name as the key and the
  • clean value as the value.
  • --->
  • <cfloop
  • index="setCondition"
  • array="#setConditions#">
  •  
  • <!---
  • The key wll be everything before the "="
  • operator (which is then trimmed to create
  • a valid column name.
  • --->
  • <cfset setColumn = trim(
  • listFirst( setCondition, "=" )
  • ) />
  •  
  • <!---
  • the value wlil be everything after the "="
  • operator (which is then trimmed to ensure
  • valid data types).
  • --->
  • <cfset setValue = trim(
  • listRest( setCondition, "=" )
  • ) />
  •  
  • <!---
  • Store the sanitized set condition in the
  • collection using the column as the key.
  • --->
  • <cfset setCollection[ setColumn ] = setValue />
  •  
  • </cfloop>
  •  
  • <!---
  • Now that we have our SQL statement, let's grab a
  • duplicate of the target query. We need to get a
  • duplicate because we will need to modify the query
  • internally.
  • --->
  • <cfset targetQuery = duplicate( caller[ sqlParts.table ] ) />
  •  
  • <!---
  • Because we know nothing about our target query, we
  • need to add an internal ID column such that we can
  • refer to the a column in a qurey-unique way.
  • --->
  • <cfset queryAddColumn(
  • targetQuery,
  • "id__internal",
  • "cf_sql_integer",
  • arrayNew( 1 )
  • ) />
  •  
  • <!---
  • Now that we have the new internal ID column, we
  • need to populate it; we're going to use the record
  • index as the ID of the record.
  • --->
  • <cfloop query="targetQuery">
  •  
  • <!--- Set the ID as the record index. --->
  • <cfset targetQuery[ "id__internal" ][ targetQuery.currentRow ] = javaCast( "int", targetQuery.currentRow ) />
  •  
  • </cfloop>
  •  
  • <!---
  • Query the records from the internal query that
  • match the given WHERE condition. Since we are
  • using a query of queries here, we can sipmly throw
  • the same WHERE clause in. As we do so, however, we
  • only want to select the columns that are being
  • updated in the SET statement - this will help us
  • when we merge the columns back into the original
  • query.
  • --->
  • <cfquery name="updatedRows" dbtype="query">
  • SELECT
  • id__internal
  •  
  • <cfloop
  • item="column"
  • collection="#setCollection#">
  •  
  • <!---
  • Since we have our internal ID column
  • being selected aboce, we know that we
  • can always include this comman.
  • --->
  • ,
  •  
  • <!---
  • Get the set value for this condition.
  •  
  • NOTE: We need this intermediary
  • variable to get around a compile-time
  • bug in the preserveSingleQuotes()
  • method that won't allow us to work on
  • "complex" values.
  • --->
  • <cfset setValue = setCollection[ column ] />
  •  
  • <!---
  • Use the new "set" value as the
  • calculated value of this new column
  • as we select it. This allows us to use
  • static values AS WELL AS values that
  • are based on other column values.
  • --->
  • ( #preserveSingleQuotes( setValue )# )
  • AS [#column#]
  • </cfloop>
  • FROM
  • targetQuery
  •  
  • <!---
  • Check to see if there was a WHERE clause
  • defined in the original query.
  • --->
  • <cfif structKeyExists( sqlParts, "where" )>
  •  
  • <!--- Include original WHERE clause. --->
  • WHERE
  • #preserveSingleQuotes( sqlParts.where )#
  •  
  • </cfif>
  • </cfquery>
  •  
  •  
  • <!---
  • At this point, our updatedRows query contains all
  • of the correctly updated columns for the records
  • that were matching in our original target query.
  • Now, we need to merge those rows back into the
  • original query.
  •  
  • We're going to loop over the target query and the
  • updatedRows query simultaneously. Because the
  • query of query selects rows in order by default,
  • we know that the order of our internal IDs should
  • have remained in tact.
  • --->
  •  
  • <!---
  • Set an index value to keep track of our updatedRows
  • index as we loop over the original query.
  • --->
  • <cfset updatedRowsIndex = 1 />
  •  
  • <!--- Loop over the original query. --->
  • <cfloop query="targetQuery">
  •  
  • <!---
  • Check to see if we have gone beyond the bounds
  • of our updated row query. If we have, then we
  • have applied all of the updates.
  • --->
  •  
  • <cfif (updatedRowsIndex gt updatedRows.recordCount)>
  •  
  • <!---
  • No more updated to merge back into the new
  • query - break out of the target query loop.
  • --->
  • <cfbreak />
  •  
  • </cfif>
  •  
  • <!---
  • Check to see if we need to update the current
  • row of the target query based on whether the
  • internal ID of the internal query matches the
  • internal ID of the next available update row.
  •  
  • NOTE: Remembder, since our internal IDs are
  • in order, this should be fine.
  • --->
  • <cfif (targetQuery.id__internal eq updatedRows[ "id__internal" ][ updatedRowsIndex ])>
  •  
  • <!---
  • We need to merge the updated columns back
  • into the orginal query. To do that, all
  • we have to do is move the columns from
  • the updated rows query into the target
  • query. Since we only selected the updated
  • columns in the updated rows query, this
  • will only copy over what is needed.
  • --->
  • <cfloop
  • index="column"
  • list="#updatedRows.columnList#"
  • delimiters=",">
  •  
  • <cfset targetQuery[ column ][ targetQuery.currentRow ] = updatedRows[ column ][ updatedRowsIndex ] />
  •  
  • </cfloop>
  •  
  • <!---
  • Increment the updated rows index so that
  • the next target query record will be
  • compared to the next available update.
  • --->
  • <cfset updatedRowsIndex++ />
  •  
  • </cfif>
  • </cfloop>
  •  
  • <!---
  • At this point, we have merged the updated columns
  • back into the internal verion of our target query.
  • The problem now is that we have an extra internal
  • ID column. To get rid of it, and to updated the
  • original query, we are going to select the records
  • of the internal query into the original query
  • value, selecting only the originally present
  • columns.
  • --->
  •  
  • <!---
  • Get the column array from the original query.
  • Because we have altered our version, we have to
  • reach back out into the caller scope.
  • --->
  • <cfset columns = listToArray(
  • caller[ sqlParts.table ].columnList
  • ) />
  •  
  • <!---
  • Select the appropriate columns from our internal
  • query into the original query reference.
  • --->
  • <cfquery name="caller.#sqlParts.table#" dbtype="query">
  • SELECT
  • [#arrayToList( columns, "],[" )#]
  • FROM
  • targetQuery
  • </cfquery>
  •  
  • <cfelse>
  •  
  • <!---
  • The UPDATE statement that the user provided was
  • not valid. Throw an error.
  • --->
  • <cfthrow
  • type="InvalidSqlStatement"
  • message="Your SQL statement is invalid."
  • detail="The SQL UPDATE statement you provided cannot be used with this query of query tag."
  • />
  •  
  • </cfif>
  •  
  •  
  • <cfelseif reFind( "(?i)^DELETE", sqlStatement )>
  •  
  •  
  • <!---
  • The user wants to perform a DELETE statement. Let's
  • create a regular expression pattern than will help us
  • grab the necessary parts of the query.
  • --->
  • <cfsavecontent variable="regexPattern"
  • >(?xi)
  •  
  • # The delete statement. We don't need to capture this
  • # since we know what kind of statement we are doing.
  •  
  • ^DELETE \s+ FROM \s+
  •  
  • # The name of the table (variable) that we are going
  • # to be deleting records from.
  •  
  • ([\w_.]+) \s+
  •  
  • # The entire WHERE clause is optional.
  •  
  • (?:
  •  
  • # WHERE keyword. No need to capture this.
  •  
  • WHERE \s+
  •  
  • # Now, we need to capture the set of conditions.
  • # We will later use this to delete from the query
  • # object manually.
  •  
  • (
  • # There must be at least one where statement.
  •  
  • (?:
  • [^=]+ = \s*
  • (?:
  • [^',]
  • |
  • '[^']*(?:''[^']*)*'
  • )+
  • ,? \s*
  • )+
  • )
  •  
  • )?
  • </cfsavecontent>
  •  
  • <!--- Compile the pattern. --->
  • <cfset pattern = patternClass.compile(
  • javaCast( "string", regexPattern )
  • ) />
  •  
  • <!---
  • Get the matcher for the pattern using the sql
  • statement. This will give us access to the
  • captured groups.
  • --->
  • <cfset matcher = pattern.matcher(
  • javaCast( "string", sqlStatement )
  • ) />
  •  
  • <!---
  • Check to see if the pattern can be found in the
  • user-provided SQL statement.
  • --->
  • <cfif matcher.find()>
  •  
  • <!--- Get the SQL parts. --->
  • <cfset sqlParts = {
  • table = matcher.group( javaCast( "int", 1 ) ),
  • where = matcher.group( javaCast( "int", 2 ) )
  • } />
  •  
  • <!---
  • Now that we have our SQL parsed, let's update the
  • query. A DELETE command can be thought of a SELECT
  • command that updates the target query object with
  • all records that do NOT match the given conditions.
  • --->
  •  
  • <!---
  • Get a local reference to the query so we don't
  • end up with too many dot-delimiters in our table
  • path (which can cause a syntax error).
  • --->
  • <cfset targetQuery = caller[ sqlParts.table ] />
  •  
  • <!---
  • Now, let's select all the rows that DONT match the
  • WHERE conditions and overide the original query
  • with the new result set.
  •  
  • NOTE: This WILL break any other references to this
  • query object; however, there are no defined methods
  • in ColdFusion that can be used to remove a row.
  • --->
  • <cfquery name="caller.#sqlParts.table#" dbtype="query">
  • SELECT
  • *
  • FROM
  • targetQuery
  •  
  • <!---
  • Check to see if we have any WHERE conditions.
  • If we don't then that means we want to remove
  • ALL rows from this query.
  • --->
  • <cfif structKeyExists( sqlParts, "where" )>
  •  
  • <!---
  • We are NOT'ing the collection of
  • conditions here because we want to
  • delete all the rows where the
  • condition matches. As such, we want to
  • select only the rows where the entire
  • condition does NOT match.
  • --->
  • WHERE
  • NOT
  • (
  • <!---
  • Because we are using a query of
  • query here, we can simply include
  • the WHERE clause from the original
  • DELETE SQL.
  • --->
  • #preserveSingleQuotes( sqlParts.where )#
  • )
  •  
  • <cfelse>
  •  
  • <!---
  • Since no WHERE conditions were provided,
  • we want to delete all conditions. As such,
  • let's provide a WHERE condition that will
  • always be false (which will not select
  • any rows, and therefore empty the target
  • query).
  • --->
  • WHERE
  • 1 = 0
  •  
  • </cfif>
  • </cfquery>
  •  
  • <cfelse>
  •  
  • <!---
  • The UPDATE statement that the user provided was
  • not valid. Throw an error.
  • --->
  • <cfthrow
  • type="InvalidSqlStatement"
  • message="Your SQL statement is invalid."
  • detail="The SQL UPDATE statement you provided cannot be used with this query of query tag."
  • />
  •  
  • </cfif>
  •  
  •  
  • <cfelse>
  •  
  •  
  • <!---
  • If we made it this far, then the user has a SQL
  • statement that we don't know how to parse. Throw
  • an error.
  • --->
  • <cfthrow
  • type="InvalidSqlStatement"
  • message="Your SQL statement is invalid."
  • detail="The SQL statement you provided cannot be used with this query of query tag."
  • />
  •  
  •  
  • </cfif>
  •  
  •  
  • <!--- Clear the tag content. --->
  • <cfset thistag.generatedContent = "" />
  •  
  • </cfif>

If you take the time to look through the code above, you will see that each UPDATE and DELETE statement is powered internally by a SELECT statement. While there are other ways to do this, using the SELECT functionality was the only "easy" way to leverage the robust syntax supported by query of queries.

The biggest shortcoming of the technique that I have provided above is that the resultant query always replaces the existing query object variable. In a single page of processing, this is not an issue; however, if an application had cached queries, references to those queries would not point to the resultant query of the UPDATE and DELETE statements. Meaning, the variable you are querying would be fine, but any additional references to it would be outdated.

So anyway, "Ask Ben" questions inspire me just as much as they (hopefully) help you! That said, thanks for asking me questions.




Reader Comments

ohh thankss :))

Very beautifull place , thanks...
ohh thankss :))

Very beautifull place , thanks...

Reply to this Comment

Wow, very nice piece of code and a very cool way to turn around the available features!! Please refine this further and release it, it is very impressive!

Reply to this Comment

Great stuff Ben as always and Q of Q is very powerful. The error handling in your custom tag is important too. As I spend all my life, well working life, solving failure situations relating to performance, I have often found Q of Q, as it stands to be lacking in adequate error reporting detail, perhaps that is an opportunity in itself?

Reply to this Comment

This blog I have been reading for almost 3 years. I am commenting again on this particular post after almost one year.

Ben, This is totally creative and hats off to you. Few may think what a big deal but if someone who have experience with SQL and ColdFusion both, can only truly appreciate the depth of this suggestion.

What additionally, impresses me completeness of Ben's script. All with proper comments and following standard. There are no cut corners or anything. A very organized scripts.

Kind Regards,
Pinal

Reply to this Comment

A while ago I had a project where I did much of the same thing in QoQ -- faux-deletes and faux-updates:

http://rickosborne.org/blog/index.php/2008/11/01/quine-mccluskey-in-mostly-sql-with-cf-qoq/

http://code.google.com/p/nowrists/source/browse/trunk/NoWriSts/logic/quinemccluskey.cfm

It certainly would be nice if QoQ supported updates and deletes natively.

Now that I've had to go through and teach classes on the stuff, if I had to do it again, I'd probably do it in a Derby in-memory database, since CF8 supports them natively.

Reply to this Comment

Thanks guys. This was a lot of fun to work on. On the train ride up here (I'm in Boston at jQuery conference), I went through like 3 iterations of the logic. I first started to try to implement conditional logic in CF, using CFIF statements and what not. But, then I realized it would be much more robust to leverage the existing SELECT behavior. There is more processing overhead, but much more flexibility.

@Rick,

Your SQL is very intense. The stuff you have in your head blows my mind.

Reply to this Comment

Maybe I am missing something but QofQs supports UNIONS so you can do a psuedo update like

UPDATE MyQuery
SET MyField='#DefaultValue#'
WHERE MyField=''

with a QofQ Query like:

<cfquery name="MyQuery" dbtype="query">
SELECT ID,MyField
FROM MyQuery
WHERE MyField<>''
UNION
SELECT ID,'#DefaultValue#'
FROM MyQuery
WHERE MyField=''
</cfquery>

which eliminates needing to merge the changes back in, of course you can also have the new query have a seperate name to not overwrite the original. I have used similar code in shopping cards that store the ordered items in a query in a cfc in the session scope, where to change the quantity on a product I can do the following

SELECT ProductID, Quantity, Name, Price
FROM This.Cart
WHERE ProductID<>#Arguments.ProductID#
UNION
SELECT ProductID, #Arguments.Quantity#, Name, Price
FROM This.Cart
WHERE ProductID=#Arguments.ProductID#

The only caveat is that you need to explicitly reference all the fields or the replacement values in both queries that make up the union so they are included in the resulting query

So a custom tag could be done like:
SELECT #Query.ColumnList#
FROM Query
WHERE NOT([Original Where Clause])
UNION
SELECT
<cfloop list="#Query.ColumnList#" item="ThisField">
<cfif ThisField in Set Statements>
[The new value from the set statement]
<cfelse>
#ThisField#
</cfif>
[output a comma if not the last field]
</cfloop>
FROM Query
WHERE [Original Where Clause]

Am I missing something?

Reply to this Comment

@David,

That's a really awesome idea. The only thing I would be concerned about would be the ordering. Doing the UNION would change the order of the final query; by doing the merge, I *think* I was able to maintain the original order of the records.

Really though, a great idea.

Reply to this Comment

If you need to maintain order you could add a field to the query that is equivalent to the currentrow value like your "id__internal" field is, then use it in an order by clause with the union

QueryAddColumn(Leads,"RowNum__","Integer",ArrayNew(1))]
[cfloop index="RowNum" from="1" to="#Leads.RecordCount#"]
[cfset RowNumbers[RowNum]=RowNum]
[/cfloop]
[cfset QueryAddColumn(Leads,"RowNum__","Integer",RowNumbers)]

[cfquery name="Query" dbtype="query"]
SELECT ...,RowNum__
FROM Query
WHERE LastName<>'Smith'
UNION
SELECT ...,RowNum__
FROM Query
WHERE LastName='Smith'
ORDER BY RowNum__
[/cfquery]

If you don't want the added column you could simply requery without it in the list

[cfquery name="Query" dbtype="query"]
SELECT ...
FROM Query
[/cfquery]

The only question is whether using a union and letting the QofQ do the work or looping over the query and updating individually would perform better, my instinct is that the QofQ would likely perform better being builtin but haven't tested in the least so I could be completely wrong, would possibly depend on the number of rows/columns updated as well as size of query

Reply to this Comment

@David,

There is definitely something really nice about your idea. I'll play around with it some more when I have some time.

Reply to this Comment

Cool work, i found this article by coincidence... I'm lookin for sum like this Where were you ben :)

Thanx you a lot (Y)

Reply to this Comment

Just in case you didnt already know this..You are a bad ass. This code saved me big time.

Many thanks.

Reply to this Comment

Hey Ben, I'm trying to do something like:

<cf_qoq>
DELETE FROM pmu
WHERE pmu_Zip LIKE '#imhere#%'
</cf_qoq>

Is that possible here? All your delete examples used "="

Reply to this Comment

@Anthony, @Dan,

I haven't looked at this in a while, but quickly looking at the code, it looks like the DELETE statements requires "=" where as the UPDATE statement is a bit more lenient. When I was writing this, I probably just didn't think that I would need anything but an equals in DELETE.

You could probably update it to work better in each case; it's just a matter of tweaking the regular expression... which may or may not be easy - they are kind of big at this point.

Reply to this Comment

I just had another idea as well:

We can change qoqs by looping over the query and doing this:

[cfset ROWNR=1]
[cfloop query="q_myQuery"]
[cfset q_myQuery.F_FIELD[ROWNR]='AnyContent']
[cfset ROWNR++]
[/cfloop]

-- If I want to change only one field of a specific row (ID=17)

[cfset ROWNR=1]
[cfloop query="q_myQuery"]
[cfif q_myQuery.ID_ROW eq 17]
[cfset q_myQuery.F_FIELD[ROWNR]='HALLO']
[/cfif>
[cfset ROWNR++]
[/cfloop]

Until now I always used the UNION-like solution.
But this is a good alternative for me too.

By the way, I am a big fan of Ben and these posts helped me already many times...

Thanks a lot for all!!

Janine

Reply to this Comment

Hello all,I am new and I would like to ask that what are the benefits of sql training, what all topics should be covered and it is kinda bothering me ... and has anyone studies from this course wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance...
would really appreciate help... and Also i would like to thank for all the information you are providing on sql.

Reply to this Comment

Hi Ben. Very elegant solution. I was wondering is there a way to execute it withing a cfscript

Reply to this Comment

@Erik,

I think you'd need a tag based cfc wrapper.

<cffunction access="public" returntype="query" name="QtheQ">
<cfproperty name="queryName" type="string" required="true">
<cfproperty name="SQL" type="string" required="true">
<cf_qoq>#arguments.SQL#</cf_qoq>
<cfreturn evaluate(queryName)>
</cffunction>

then you could invoke

girls = cfcName.QtheQ(queryName="girls",SQL="update girls...");

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.