Database Reference
In-Depth Information
The statement just shown would work fine, but we also need to delete the related entry in
the
prize_winners
table. So we should first get the
human_id
for this row before
deleting it. That's why I said we won't enter this SQL statement. It's tedious, though, to
execute one SQL statement to retrieve the
human_id
, then another to delete the row in
the
humans
table, and then execute a third SQL statement to delete the related row in the
prize_winners
table. Instead, it would be better to change the
DELETE
statement to
include both tables, deleting the desired rows from both in one SQL statement. We'll cov-
er that in the next subsection.
Deleting in Multiple Tables
There are many situations where data in one table is dependent on data in another table. If
you use
DELETE
to delete a row in one table on which a row in another table is depend-
ent, you'll have orphaned data. You could execute another
DELETE
to remove that other
row, but it's usually better to delete rows in both tables in the same
DELETE
statement,
especially when there may be many rows of data to delete.
The syntax for the
DELETE
that deletes rows in multiple tables is:
DELETE FROM
table
[,
table
]
USING
table
[, . . . ]
[WHERE
condition
];
In the
FROM
clause, list the tables in a comma-separated list. The
USING
clausespecifies
how the tables are joined together (e.g., based on
human_id
). The
WHERE
clauseis op-
tional. Like the
UPDATE
statement, because this syntax includes multiple tables, the
ORDER BY
and
LIMIT
clauses are not permitted. This syntax can be tricky, but how
much so may not be evident from looking at the syntax. Let's look at an example.
In the example at the end of the previous subsection, we needed to delete rows from two
tables that are related. We want to delete the rows for
Elena Bokova
in which she has a
ya-
hoo.com
email address in both the
humans
and the
prize_winners
tables. To do that
efficiently, we'll enter this from the
mysql
client:
DELETE FROM
humans
,
prize_winners
USING
humans
JOIN
prize_winners
WHERE
name_first
=
'Elena'
AND
name_last
=
'Bokova'
AND
email_address
LIKE
'%yahoo.com'
AND
humans
.
human_id
=
prize_winners
.
human_id
;
This
DELETE
statement is similar to other data manipulation statements (e.g.,
SELECT
,
UPDATE
). However, there is a difference in the syntax that may be unexpected and con-
