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, ...
Get Programming the Perl DBI now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.