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

set the column to a value of 2 to indicate they are a member of both sites. Because two
people can have the same name, we use the email address to determine whether a row is a
duplicate. In the humans table, the email_address column is already set to UNIQUE .
It will be the basis by which rows will be updated with the combined SQL statement we'll
use. With these factors in mind, let's try to insert a few members:
INSERT INTO humans
( formal_title , name_first , name_last , email_address ,
better_birders_site )
VALUES ( 'Mr' , 'Barry' , 'Pilson' , '[e-mail]' , 1 ),
( 'Ms' , 'Lexi' , 'Hollar' , '[e-mail]' , 1 ),
( 'Mr' , 'Ricky' , 'Adams' , '[e-mail]' , 1 )
ON DUPLICATE KEY
UPDATE better_birders_site = 2 ;
Because of the ON DUPLICATE KEY component, when there are rows with the same
email address, the better_birders_site column will be set to 2. The rest will be
inserted with their better_birders_site column set to 1. That's what we wanted.
We now need to insert rows for these new members in the prize_winners table. We'll
use the INSERT...SELECT statement aswe did earlier, but this time we'll just insert
rows where the value of the better_birders_site column is 1:
INSERT INTO prize_winners
( human_id )
SELECT human_id
FROM humans
WHERE better_birders_site = 1 ;
Although these two SQL statements worked well, it's possible that there might be two
entries for someone in the humans table if they used a different email address on the oth-
er site. That possibility may already exist with our existing members if they registered on
the site more than once. Let's check for this possibility and add a column to note it. We'll
enter the following SQL statements to prepare:
ALTER TABLE humans
ADD COLUMN possible_duplicate TINYINT DEFAULT 0 ;
CREATE TEMPORARY TABLE possible_duplicates
( name_1 varchar ( 25 ), name_2 varchar ( 25 ));
The first statement added a column to the humans table to note a row as a possible du-
plicate entry. The second creates a temporary table. A temporary table is accessible only
to your MySQL client connection. When you exit from the client, the temporary table will