Handling Duplicates - MySQL - page 556

+---------+--------------+------------+
| poll_id | candidate_id | vote_count |
+---------+--------------+------------+
| 14 | 3 | 1 |
+---------+--------------+------------+
1 row in 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, 2 rows affected (0.00 sec)
mysql> SELECT * FROM poll_vote;
+---------+--------------+------------+
| poll_id | candidate_id | vote_count |
+---------+--------------+------------+
| 14 | 3 | 2 |
+---------+--------------+------------+
1 row in set (0.00 sec)
For the first INSERT , no row for the candidate exists, so the row is inserted. For the
second INSERT , the row exists, so MySQL just updates the vote count. With INSERT …
ON DUPLICATE KEY UPDATE , you need not check whether the row exists; MySQL does it
for you. The row count indicates what action the INSERT statement performs: 1 for a
new row and 2 for an update to an existing row.
The techniques just described have the benefit of eliminating overhead that might
otherwise be required for a transaction. But this benefit comes at the price of portability
because they all involve MySQL-specific syntax. If portability is a high priority, you
might prefer to use a transactional approach.
See Also
For bulk record-loading operations in which you use the LOAD DATA statement to load
a set of rows from a file into a table, control duplicate-row handling using the statement's
IGNORE and REPLACE modifiers. These produce behavior analogous to that of the IN
SERT IGNORE and REPLACE statements. For more information, see Recipe 11.1 .
Recipes 13.12 and 20.12 further demonstrate the use of INSERT … ON DUPLICATE KEY
UPDATE for initializing and updating counts.
16.3. Counting and Identifying Duplicates
Problem
You want to determine whether a table contains duplicates, and to what extent they
occur. Or you want to see the rows that contain the duplicated values.