Database Reference
In-Depth Information
most always use a
WHERE
clause with an
UPDATE
statement to limit updates to rows
based on certain conditions. There are times when you might also want to limit updates to
a specific number of rows. To do this, use the
LIMIT
clause with the
UPDATE
statement.
This clause functions the same as in the
SELECT
statement, but its purpose is different
with
UPDATE
. Let's look at an example of how and why you might use the
LIMIT
clause
with the
UPDATE
statement.
Suppose that we decide to offer a small prize each month to two of the members of our
site to encourage people to join. Maybe we'll offer them the choice of a booklet with a list
of birds found in their area, a nice pen with the Rookery name on it, or a water bottle with
a bird image on it. Suppose also that we want a person to win only once, and we want to
make sure that everyone wins eventually. To keep track of the winners, let's create a table
to record who won and when, as well as what prize they were sent and when. We'll use
the
CREATE TABLE
statement like so:
CREATE TABLE
prize_winners
(
winner_id
INT
AUTO_INCREMENT
PRIMARY KEY
,
human_id
INT
,
winner_date
DATE
,
prize_chosen
VARCHAR
(
255
),
prize_sent
DATE
);
In this statement, we created a table called
prize_winners
and gave it five columns:
the first (
winner_id
) is a standard identifier for each row; the second (
human_id
) is
to associate the rows in this table to the
humans
table; the third column (
win-
ner_date
) is to record the date that the winner was determined; the next
(
prize_chosen
) is the prize the member chose ultimately; and the last column
(
prize_sent
) is to record the date the prize was sent to the winner.
NOTE
The IDs in this table may be a bit confusing.
winner_id
will be used to select items from this table,
such as the prize and the dates.
human_id
will be used to find data about the winner in the
humans
table. You might think that there is no need for two IDs, as they both refer to the same person. But think
back to the ways we used IDs to link birds, bird families, and bird orders. Giving each table its own iden-
tifier is more robust.
We could have set the
prize_chosen
column to an enumerated list of the choices, but
the choices may change over time. We may eventually create another table containing a
list of the many prizes and replace this column with a column that contains a reference
number to a table listing prizes. For now, we'll use a large variable character column.
