Insert ... on duplicate key update nothing using MySQL
duplicate-data, mysql
Solution
If you want `ON DUPLICATE KEY UPDATE` to not actually do anything, just set a column value to the existing value. Other conflicts such as foreign key constraints will bubble up, unlike using the `IGNORE` keyword, but no values will change on conflict.
INSERT INTO table (value1, value2) VALUES ('1', '2')
ON DUPLICATE KEY UPDATE value1 = value1;
If you want to ensure that no valid data changes in the event of a conflict, you can add a column with arbitrary data in it to the table, and use that for the `UPDATE` statement.
A third option if you wish to keep all logic in your application and not in the database is to run a `SELECT` statement first to inspect potential conflicts before running your `INSERT/UDPATE` statement.
Although ruled out for your scenario, a stored procedure would also be able to provide this logic in a single database call.
Problem
My problem is that I have multiple unique keys on a table. - Insert ignore is not an option because it suppresses the errors. - MySQL has no support for any type of conditionals outside a statement (ex. if (cond) then insert else don't insert) - Stored procedures are not an option (the only place I can use the if/else statements) - On duplicate key might update a key with a new value, but I want the unique keys not to change in case one fails the unique constraint. So the only option would be on duplicate just don't update anything. Is there any way I can achieve this? Or are there any other options?