I’m by no means an expert using SQL, but as of yet I’ve found no way to delete data from multiple tables without writing a single query specifically for each table that data needs deleted from. So if you’re reading this expecting a native SQL statement that will delete data from multiple tables then I have nothing to offer. However, I do have a solution. One which requires a programming language, Coldfusion in my case.

The idea is that we have a number of tables.  Lets say we have five tables holding data about products.  So we’ll have tables named ‘products_tbl’, ‘products_name_tbl’, ‘products_description_tbl’.  Using just SQL we would need to write three delete statements like:

Straight SQL

<cfquery name="VARIABLES.deletetable" datasource="myds">
	DELETE
	FROM product_tbl
	WHERE product_id = 9
</cfquery>

<cfquery name="VARIABLES.deletetable" datasource="myds">
	DELETE
	FROM product_name_tbl
	WHERE product_id = 9
</cfquery>

<cfquery name="VARIABLES.deletetable" datasource="myds">
	DELETE
	FROM product_description_tbl
	WHERE product_id = 9
</cfquery>

Now let’s say that halfway through the project the client suddenly wants the price to be included. Really not a big deal, but when we now go to delete the product we realize that we need to go find our component or whatever that is handling the deleting transactions and add a new delete statement.

<cfquery name="VARIABLES.deletetable" datasource="myds">
	DELETE
	FROM product_price_tbl
	WHERE product_id = 9
</cfquery>

I personally hate this, and like most things the more complicated the data becomes the more burdensome it gets on the developer. So what’s the solution?

The idea is that we need to bundle all of these related sets of data into a group. When I say related I simply mean that in this case all the tables containing the parts and pieces of a product. The product is an object, and when that object is deleted we need to visit each of the tables containing information for that product and get rid of it. If we could simply group each of the table names we could run a loop with a delete statement inside where the table name dynamically changes with each loop. This would allow us to update our group and not have to worry about updating any actual SQL.

In my case, I’ve created the group under the APPLICATION scope. This let’s me access the group from anywhere within my application. As such my Application file would contain inside the onApplicationStart() method, for this case, the following.

<cfparam name="APPLICATION.producttables.productTbl" type="string" default="product_tbl">
<cfparam name="APPLICATION.producttables.productNameTbl" type="string" default="product_name_tbl">
<cfparam name="APPLICATION.producttables.productDescriptionTbl" type="string" default="product_description_tbl">

Now that we have our table names stored in the APPLICATION scope under a group called ‘producttables’ we can build a function that will get all the data in the ‘producttables’ group and set it into an array. We can then loop over this array of table names deleting from each the product data associated with the specified product id. In this case our function would look like.

<cfcomponent displayname="Looping Delete" hint="Loops over a portion of a struct with table names and deletes from those tables." output="false">

	<cffunction name="deleteProduct" displayname="Delete Product" description="Deletes a product." access="public" output="false" returntype="void">

		<!--- PRODUCT ID --->
		<cfargument name="productid" displayname="Product ID" hint="Id of the product to be deleted." type="numeric" required="true" />

		<!--- CREATE AN ARRAY OF TABLE NAMES STORED IN APPLICATION SCOPE --->
		<cfset var tableArray = structkeyarray(APPLICATION.producttables)>

		<!--- LOOP OVER THE ARRAY --->
		<cfloop array="#tblArray#" index="VARIABLES.i">
			<cfquery name="deleteproduct" datasource="#APPLICATION.dataSource#">
				DELETE
				FROM schema_name.#APPLICATION.producttables[VARIABLES.i]#
				WHERE product_id = <cfqueryparam value="#ARGUMENTS.productid#" cfsqltype="cf_sql_integer" maxlength="10">
			</cfquery>
		</cfloop>

	</cffunction>

</cfcomponent>

So what we’ve done here is made it much easier to add data to the product without ever having to touch our component. Instead all we really need to do is add the data to the APPLICATION scope to the producttables group. So for instance now if we need to add a price to our product all we have to do is go into the Application.cfc file and set

<cfparam name="APPLICATION.producttables.productPriceTbl" type="string" default="product_price_tbl">

That’s all that we need now to make the update. The loop will now find this table in our group and delete from it the same as the others.