Handling Duplicates - MySQL - page 555

1. Issue a SELECT to check whether a row already exists with a given email value.
2. If no such row exists, add a new one with INSERT .
3. If the row does exist, update it with UPDATE .
These steps must be performed within a transaction or with the tables locked to prevent
other users from changing the tables while you're using them. In MySQL, you can use
REPLACE to simplify both cases to the same single-statement operation:
REPLACE INTO passtbl ( email , password ) VALUES ( address , hash_value );
If no row with the given email address exists, MySQL creates a new one. Otherwise,
MySQL replaces it, in effect updating the password column of the row associated with
the address.
INSERT IGNORE and REPLACE are useful when you know exactly what values should be
stored in the table when you attempt to insert a row. That's not always the case. For
example, you might want to insert a row if it doesn't exist, but update only certain parts
of it otherwise. This commonly occurs when you use a table for counting. Suppose that
you record votes for candidates in polls, using the following table:
CREATE TABLE poll_vote
(
poll_id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
candidate_id INT UNSIGNED ,
vote_count INT UNSIGNED ,
PRIMARY KEY ( poll_id , candidate_id )
);
The primary key is the combination of poll and candidate number. The table should be
used like this:
• For the first vote received for a given poll candidate, insert a new row with a vote
count of 1.
• For subsequent votes for that candidate, increment the vote count of the existing
record.
Neither INSERT IGNORE nor REPLACE are appropriate here because for all votes except
the first, you don't know what the vote count should be. INSERT … ON DUPLICATE KEY
UPDATE works better here. The following example shows how it works, beginning with
an empty table:
mysql> SELECT * FROM poll_vote;
Empty set (0.00 sec)
mysql> INSERT INTO poll_vote (poll_id,candidate_id,vote_count) VALUES(14,3,1)
-> ON DUPLICATE KEY UPDATE vote_count = vote_count + 1;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM poll_vote;