Programming the Perl DBI

Deleting Data

Now that you have spent copious amounts of time loading data into your tables to play with, the next thing you’ll want to do is tidy it up and remove redundant or unwanted data.

The DELETE keyword defined within SQL is exactly what you are looking for, providing a simple syntax for permanently removing rows of data from tables. As with the INSERT statement, deleting rows applies only to a single table at a time; therefore, if you want to remove rows that are referred to by records in other tables, you should first delete those associated foreign key records from the secondary tables. This preserves the referential integrity of your database and is known as delete cascading.^[29] Some databases support cascading delete mechanisms that automate these extra deletes.

For example, a cascading delete applied to rows in the megaliths table would also need to remove the appropriate rows in the media table where the following join condition is true:

megaliths.id = media.megaliths_id

However, DELETE statements do not have the same “single row at a time” restriction that INSERT statements suffer from. DELETE can purge a table entirely in one statement. For example, to remove all the rows within the megaliths table, we could simply write:

DELETE FROM megaliths

Of course, we may not wish to remove all the rows from a table, but only certain rows. This can be done in a familiar manner by specifying a list of conditions that the data within a row must meet for it to be removed. Therefore, ...