Database Reference
In-Depth Information
Updating Multiple Tables
Thus far in this chapter,we have updated only one table at a time with the
UPDATE
state-
ment. We've also made updates based on the values of the table for which the changes
were made. You can also update values in one table based on values in another table. And
it's possible to update more than one table with one
UPDATE
statement. Let's look at
some examples of how and why you might do this.
Suppose that we've been giving out prizes for a couple of years now and that we've de-
cided we want to make a special bid to recruit and retain members from the United King-
dom. To do this, we've decided to give four prizes each month to members of the Rookery
site: two prizes to members in the U.K, and two prizes to members in all other countries.
We'll announce this change so that our skewing will be perceived fairly by members of
the site. We'll even allow U.K. members who won previously to win again. For this last
component, we'll need to reset the values of rows in the
prize_winners
table based
on the value of the
country_id
in the
humans
table. Let's see how that would look:
UPDATE
prize_winners
,
humans
SET
winner_date
=
NULL
,
prize_chosen
=
NULL
,
prize_sent
=
NULL
WHERE
country_id
=
'uk'
AND
prize_winners
.
human_id
=
humans
.
human_id
;
This SQL statement checks rows in one table, associates those rows to the related rows in
another table, and changes those rows in that second table. Notice that we listed the two
tables involved in a comma-separated list. We then usedthe
SET
clause to set the values
of the columns related to winning a prize to NULL. Inthe
WHERE
clause, we give the
condition that the
country_id
from the
humans
table has a value of
uk
and that the
human_id
in both tables equal.
Now that we've reset the prize information for the U.K. members, we're ready to award
prizes for the new month. Let's try the
UPDATE
statement that we used previously to ran-
domly select winners, but this time we'll straddle both the
humans
and
prize_winners
tables by entering the following:
UPDATE prize_winners, humans
SET winner_date = CURDATE()
WHERE winner_date IS NULL
AND country_id = 'uk'
AND prize_winners.human_id = humans.human_id
ORDER BY RAND()
LIMIT 2;
