| Now that we have seen how to update existing data and insert new data, that only leaves deleting data. It is quite easy to delete data from your databasetoo easy in fact. Once you delete data, it is usually gone, gone, gone, and there is no way to get it back. With that in mind, it might be better to explore some different options when you think data is no longer needed. Let's take a brief look at how we can use SQL to delete data from our database if we are sure that's really what we want to do. Then we'll discuss some different options to permanently deleting data. Using Delete SQL Using DELETE SQL is quite simple. The following code shows a typical DELETE SQL statement. DELETE FROM Employees WHERE EmployeeID=1 You simply use the DELETE FROM keywords to identify the table from which you want to delete data. This is followed by a WHERE clause that identifies the record you want to delete. This record usually is identified by reference to the field that contains the primary key value for that record. A big gotcha here is that if you forget to include a WHERE clause, the SQL statement will permanently delete every record in the table, a potentially career-ending move. Other Options Because deleting information from a database is such a permanent, irreversible action and because mistakes are so easy to make, most developers choose to handle the situation in a different fashion. If you want to exclude data that is no longer relevant, it is probably better to include a field in your database that you can use to mark a record as inactive. For example, rather than deleting an employee's record, we could include a YES/NO field in our Employees table called Inactive. When an employee leaves the company, we could simply set the value of this field to 1. Then, in our other queries, we would simply add a condition to our WHERE clause, as follows: SELECT * FROM Employees WHERE Inactive = 0 This would retrieve all employees who are still active. Handling data in this way also allows for unseen circumstances. For example, what if we had to do an audit to find out who was working for us in 1999? If we had deleted the records, there would be no way for us to use the database to figure this out. If we simply mark employees as inactive, however, their information is still on record if we ever need it. Another option would be to archive old employee information into another table within the database, using either ColdFusion or the database system itself. Always give careful thought to permanently deleting information in a database. | 
