Aug 192014
 
Article MySQL

When an INSERT statement is executed to add a set of records to a table that has a unique key defined, it may happen that the value of the key in some of the records to be inserted is the same as that of records already in the table.

The default behaviour in this case is that the execution fails with an error message “execute failed: Duplicate entry”. When this happens, no record is inserted, not even those whose keys do not clash with the keys of previously existing records.

Example:

Let’s say we are developing an application that needs a “customers” table, with columns “clientid”,”name” and “email”. There will be a unique key defined on the “clientid” field.

We can create the table, and insert the first records successfully:

Next, we want to insert two more records in the table, but the client id of one of them is already used by one of the existing records. The sentence fails with the “duplicate entry” error message.

MySQL implements some extension to the standard SQL, that allow us to treat this event in a flexible way:

INSERT IGNORE, UPDATE IGNORE

Using the IGNORE modifier, those new records that clash with existing records are discarded, while non-conflicting records are inserted in the table:

The IGNORE modifier can also be used in a UPDATE sentence. The effect is again to discard those updates that would cause a unique key conflict.

REPLACE

A REPLACE statement can be used in place of an INSERT statement. Using REPLACE, new records that do not conflict with existing records are inserted in the table as usual. When a record to be inserted causes a duplicate key conflict, the previously existing record is deleted, and the new record is inserted:

INSERT … ON DUPLICATE KEY UPDATE

The INSERT command, with the “ON DUPLICATE KEY UPDATE update-statement”, when a record to be inserted conflicts with an existing records, does not insert the new record, but executes the “update-statement” instead. This can be used to flag the existence of duplicates.

For instance, a field “total” can be added to the table customers, to keep track of the number of duplicates, as follows:

As can be seen, the values of name and email in the record with clientid=2 have not been changed, but the value of the “total” column has been increased by one.

LOAD DATA INFILE ‘filename’ [ IGNORE | REPLACE ]

The LOAD DATA INFILE command can also be used with the modifiers IGNORE and REPLACE, resulting in the same behaviour as above explained for conflicting records

 

References:

Index of posts related to MySQL

 Posted by at 11:45 am

 Leave a Reply

(required)

(required)