Database Reference
In-Depth Information
structed a
DELETE
statement that worked fine, but there was potentially a problem with
it. Here is that SQL statement again:
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
;
Compared to the
JOIN
clauses we've been using, the syntax here may look strange. This
is how it works with a
DELETE
statement. Tables from which data is deleted are listed in
the
FROM
clause, while tables used in the
WHERE
clause to provide filters to determine
which rows to delete are listed in a
USING
clause. The clause "
USING humans JOIN
prize_winners
" just tells the server that those two tables provide the columns in the
WHERE
clause.
NOTE
Don't confuse a
USING
clause,which has
JOIN
subclauses,with the
USING
operator, which can be
used in a
JOIN
clause.
As the preceding
DELETE
SQL statement is constructed, if MySQL finds a row in the
humans
table where the name and email information match, there has to be a matching
row in the
prize_winners
table for the
human_id
. If there's not a row in both,
MySQL won't delete the row in the
humans
table and no error will be returned — you
might not realize it failed. To allow for this possibility, we coulduse a
LEFT JOIN
like
so:
DELETE FROM
humans
,
prize_winners
USING
humans
LEFT JOIN
prize_winners
ON
humans
.
human_id
=
prize_winners
.
human_id
WHERE
name_first
=
'Elena'
AND
name_last
=
'Bokova'
AND
email_address
LIKE
'%yahoo.com'
;
Notice that for this syntax we moved the valuation of the
human_id
columnsto the
USING
clause, adding a
LEFT JOIN
and an
ON
operator toreplace that condition inthe
WHERE
clause. That's necessary because if there's not a match in the other table, the
WHERE
clause won't include that row in the results to be deleted. With the
LEFT JOIN
,
all of the rows in both the
humans
and the
prize_winners
tables that match the cri-
