Database Reference
In-Depth Information
Deleting Data
With most databases, you willeventually need to delete rows from a table. To do this, you
can use the
DELETE
statement. As mentioned a few times earlier in this topic, there is no
UNDELETE
or
UNDO
statement for restoring rows that you delete. You can recover data
from backups, if you're making backups as you should, but it's not quick and easy to re-
store data from them. If you use a storage engine like InnoDB, there is a method for wrap-
ping SQL statements in a transaction that can be rolled back after you delete rows.
However, once you commit such a transaction, you'll have to look to backups or other
cumbersome methods to restore deleted data. Thus, you should alwaysbe careful when us-
ing the
DELETE
statement.
The
DELETE
statement works much like the
SELECT
statement in that you may delete
rows based on conditions in the
WHERE
clause.You should always use the
WHERE
clause,
unless you really want to leave an empty table with no rows. You may also include an
ORDER BY
clause to specify the order in which rows are deleted, and a
LIMIT
clause to
limit the number of rows deleted in a table. The basic syntax of the
DELETE
statement is:
DELETE FROM
table
[WHERE
condition
]
[ORDER BY
column
]
[LIMIT row_count];
As the formatting indicates with square brackets, the
WHERE
,
ORDER BY
, and
LIMIT
clauses are optional. There are additional options that may be given and deviations to the
syntax for deleting rows in multiple tables and for deletions based on multiple tables. Let's
look at an example using this simpler syntax for now.
Suppose after sending out a notice to members who we suspect of having duplicate entries
in the
humans
table, one of them confirms that her membership has been duplicated. The
member,
Elena Bokova
from Russia, asks us to delete the entry that uses her old
yahoo.com
email address. To do that, we could, but we won't, enter this SQL statement:
DELETE FROM
humans
WHERE
name_first
=
'Elena'
AND
name_last
=
'Bokova'
AND
email_address
LIKE
'%yahoo.com'
;
This SQL statement will delete any rows in which the criteria expressed in the
WHERE
clause are met. Notice that for checking the email address, we used the
LIKE
operator and
the wildcard (i.e.,
%
) to match any email ending with
yahoo.com
.
