INSERT ON DUPLICATE KEY UPDATE

suggest change
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() 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).

Feedback about page:

Feedback:
Optional: your email if you want me to get back to you:



Table Of Contents