Programming ColdFusion

Deleting Records

The CFQUERY tag can be used to delete records from a data source using the SQL DELETE clause. You can use DELETE to delete a single record or multiple records, depending on how you write the code. Once a record has been deleted, it can’t be recovered, so be extremely careful when writing code that deletes records, as it is quite easy to accidentally delete the contents of an entire table. Because of this, I recommend testing your code against a test database or a temporary table before deploying it in a live application.

Deleting a Single Record

Deleting a single record is as simple as it gets. The SQL for the delete looks something like this:

DELETE FROM tablename
WHERE primary_key = #primary_key#

We simply instruct the database to delete the record from the specified table where the primary key matches the one we supply. If there is such a record in the database, it is deleted. In this case, the primary key is a numeric value called ID. Primary keys don’t have to be numeric values, but they do have to be unique.

If you look back at Example 5-7, you’ll recall that it supports the ability to delete a record instead of populating the update form, if the appropriate parameter is passed from the record selection template. The code used to delete the record is quite simple:

<CFQUERY NAME="DeleteRecord" DATASOURCE="ProgrammingCF">
         DELETE FROM EmployeeDirectory
         WHERE ID = #ID#

To better illustrate the technique of deleting a record, let’s look at a single template, ...