INSERT INTO `table_name`
(`index_field`, `other_field_1`, `other_field_2`)
VALUES
('index_value', 'insert_value', 'other_value')
ON DUPLICATE KEY UPDATE
`other_field_1` = 'update_value',
`other_field_2` = VALUES(`other_field_2`);
This will INSERT
into table_name
the specified values, but if the unique key already exists, it will update the other_field_1
to have a new value.
Sometimes, when updating on duplicate key it comes in handy to use [VALUES()](<http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_values>)
in order to access the original value that was passed to the INSERT
instead of setting the value directly. This way, you can set different values by using INSERT
and UPDATE
. See the example above where other_field_1
is set to insert_value
on INSERT
or to update_value
on UPDATE
while other_field_2
is always set to other_value
.
Crucial for the Insert on Duplicate Key Update (IODKU) to work is the schema containing a unique key that will signal a duplicate clash. This unique key can be a Primary Key or not. It can be a unique key on a single column, or a multi-column (composite key).