MySQL INSERT INSERT, ON DUPLICATE KEY UPDATE

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

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).



Got any MySQL Question?