Insert ... on duplicate key update nothing using MySQL

11
votes

My problem is that I have multiple unique keys on a table.

  1. Insert ignore is not an option because it suppresses the errors.
  2. MySQL has no support for any type of conditionals outside a statement (ex. if (cond) then insert else don't insert)
  3. Stored procedures are not an option (the only place I can use the if/else statements)
  4. 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?

2
mysqlduplicate-data
Why are stored procedures not an option? – juergen d
and why on duplicate key might update a key with a new value? – John Woo
What do you want to happen if there is a duplicate key? Do you need to know that there was a conflict? – doublesharp
@doublesharp If there is a duplicate key dont do nothing ... But i need to know if there was any other conflict except duplicate (ex. fk constraint). – Tudor
@JohnWoo Thats the problem , i simply can't leave the update stmt empty. I need to pass some attribute that has to change. – Tudor

2 Answers

21
votes

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.

-2
votes

Found another option in case someone stumbles across this issue.

If your table has an autoincremented primary key , you can update the pk like this : 

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