Updating and Deleting Data - Learning MySQL and MariaDB - page 167

Because we want to make sure every member wins eventually, we'll enter a row in the
prize_winners table for each member. Otherwise, we would enter a row only when
the member won. This is probably the better choice for maintaining the data, but we'll use
the more straightforward method of inserting an entry for each member in the
prize_winners table. We'll use an INSERT...SELECT statementto select the win-
ners and insert them in the new table (this type of SQL statement was covered in Inserting
Data from Another Table ) :
INSERT INTO prize_winners
( human_id )
SELECT human_id
FROM humans ;
This inserted a row in the prize_winners table for each member in the humans
table. It added only the value of the human_id column, because that's all we need at this
point as no one has yet to win anything. The statement also automatically sets the win-
ner_id column, thanks to its AUTO_INCREMENT modifier, giving it a unique value for
each human. There is no reason this ID should be the same as the human_id column, be-
cause we'll use the human_id column whenever we need information from it. The other
columns currently have NULL for their values. We'll update those values when someone
wins a prize.
Now that we have a separate table for recording information about winners and their
prizes each month, let's pick some winners. We'll do that in the nextsubsection.
Ordering to Make a Difference
In the previous subsection,we decided to award prizes to members so as to encourage
new people to join the Rookery site, as well as to make current members feel good about
continuing their membership. So that new and old members have an equal chance of win-
ning, we'll let MySQL randomly choose the winners each month. To do this, we'll use the
UPDATE statement with the ORDER BY clause and the RAND() function. This function
picks an arbitrary floating-point number for each row found by the SQL statement in
which it's used. By putting this function in the ORDER BY clause, we will order the res-
ults based on the random values chosen for each row. If we couple that with the LIMIT
clause, we can limit the results to a different pair of rows each month we select winners:
UPDATE prize_winners
SET winner_date = CURDATE ()
WHERE winner_date IS NULL
ORDER BY RAND ()
LIMIT 2 ;