Deleting Data

You use a cfquery tag with a SQL DELETE statement to delete data from a database.

Deleting a single record

To delete a single record, use the table's primary key in the WHERE condition of a SQL DELETE statement. In the example, the Emp_ID field is the primary key, so the SQL Delete statement is as follows:

DELETE FROM Employee WHERE Emp_ID = #Form.Emp_ID#

You often want to see the data before you delete it. The following example displays the data to be deleted by reusing the form page used to insert and update data. Any data that you enter in the form before submitting it is not used, so you can use a table to display the record to be deleted instead.

To delete one record from a database:

Open the file updateform.cfm in ColdFusion Studio.
Change the title to "Delete Form" and the text on the submit button to "Delete Record".

Change the form tag so that it appears as follows:

<form action="deleteaction.cfm" method="Post">

Save the modified file as deleteform.cfm.
Create a new application page in ColdFusion Studio.

Enter the following code:

<cfquery name="DeleteEmployee"

  datasource="CompanyInfo">

  DELETE FROM Employee

  WHERE Emp_ID = #Form.Emp_ID#

</cfquery>



<html>

<head>

  <title>Delete Employee Record</title>

</head>

<body>

<h3>The employee record has been deleted.</h3>

<P><cfoutput>

You have deleted #Form.FirstName# #Form.LastName# from the Employees database.

</cfoutput></P>

</body>

</html>

Save the page. as deleteaction.cfm.
View deleteform.cfm a browser by specifying the page URL and an Employee ID, for example, http://localhost/myapps/updateform.cfm?Emp_ID=3. and click the Submit button.

The employee is deleted from the Employee table and the message displays.

Reviewing the code

The following table describes the code and its function:

Code

Description

<cfquery name="DeleteEmployee" datasource="CompanyInfo"> DELETE FROM Employee WHERE Emp_ID = #Form.Emp_ID# </cfquery>
Delete the record in the database whose Emp_ID column matches the Emp_ID (hidden) field on the form. Since the Emp_ID is the table's primary key, only one record gets deleted.

<cfoutput> You have deleted #Form.FirstName# #Form.LastName# from the Employees database. </cfoutput>
Inform the user that the record was deleted.

Code	Description
<cfquery name="DeleteEmployee" datasource="CompanyInfo"> DELETE FROM Employee WHERE Emp_ID = #Form.Emp_ID# </cfquery>	Delete the record in the database whose Emp_ID column matches the Emp_ID (hidden) field on the form. Since the Emp_ID is the table's primary key, only one record gets deleted.
<cfoutput> You have deleted #Form.FirstName# #Form.LastName# from the Employees database. </cfoutput>	Inform the user that the record was deleted.

Deleting multiple records

You can use a SQL condition to delete several records. The following example deletes the records for everyone in the Sales department (which has Dept_ID number 4) from the Employee table:

DELETE FROM Employee

WHERE Dept_ID = 4

To delete all the records from the Employee table, you use the following code:

DELETE FROM Employee

Note

Deleting records from a database is not reversible. Use DELETE statements carefully.

Developing ColdFusion Applications
Updating Your Database