Paste ... when duplicating a key update do not use anything with MySQL

Question

My problem is that I have some unique keys in the table.

• The ignore insert is not an option because it suppresses errors.
• MySQL does not support any type of conditional expression outside the statement (for example, if (cond) and then insert else without inserting)
• Stored procedures are not an option (the only place I can use if / else statements)
• In a double key, you can update the key with a new value, but I want the unique keys not to change if one of them could not fulfill the unique constraint.

Thus, the only option will be duplicated, just do not update anything. Is there any way to achieve this? Or are there other options?

Answer 1

If you want ON DUPLICATE KEY UPDATE actually do nothing, just set the column value to the existing value. Other conflicts, such as foreign key constraints, will bubble, unlike using the IGNORE keyword, but no values ​​will change in the conflict.

 INSERT INTO table (value1, value2) VALUES ('1', '2') ON DUPLICATE KEY UPDATE value1 = value1; 

If you want to avoid reliable data in the event of a conflict, you can add a column with arbitrary data to the table in the table and use this for the UPDATE .

The third option, if you want to save all the logic in your application, and not in the database, first run the SELECT to check for potential conflicts before running your INSERT/UDPATE .

Although there is an exception for your scenario, the stored procedure will also be able to provide this logic in a single database call.

Answer 2

Another option is found if someone stumbles on this issue.

If your table has a primary key with auto-increments, you can update pk as follows:

 INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;