Insert ... on duplicate key update nothing using MySQL

Question

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?

and why on duplicate key might update a key with a new value? — John Woo
– John Woo, Commented Oct 23, 2012 at 23:52
What do you want to happen if there is a duplicate key? Do you need to know that there was a conflict? — doublesharp
– doublesharp, Commented Oct 23, 2012 at 23:52
@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
– Tudor, Commented Oct 23, 2012 at 23:56
@JohnWoo Thats the problem , i simply can't leave the update stmt empty. I need to pass some attribute that has to change. — Tudor
– Tudor, Commented Oct 23, 2012 at 23:59

doublesharp · Accepted Answer · 2012-10-24 00:36:54Z

23

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.

edited Oct 24, 2012 at 0:36

answered Oct 23, 2012 at 23:55

doublesharp

27.9k6 gold badges56 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Tudor Over a year ago

How do i know that value 2 is the duplicate one ? If value 2 is dupliate is ok to update it , but dont update value 1 if value 2 is duplicate. If a unique constrain fails dont update any of the unique attributes

doublesharp Over a year ago

You can choose a field without a unique constraint for the UPDATE, or if you really want to make sure no important fields change, add another column with bunk data and use that in your UPDATE statement.

Tudor Over a year ago

yea was thinking of adding an extra null attribute, but thought there might be another solution. Hate this mysql limitations.

doublesharp Over a year ago

To be fair, this kind of logic is supported by MySQL, you just have to use a stored procedure.

Tudor Over a year ago

It gives alot more overhead to keep track of alot of code on the db side . Especally when you have to keep track of alot of triggers / events/ routines . I droped all of them. Prefer to change the db design and keep more of the logic on the app layer .

|

Tudor · Accepted Answer · 2012-10-24 00:53:17Z

-2

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;

answered Oct 24, 2012 at 0:53

Tudor

1,1812 gold badges13 silver badges28 bronze badges

2 Comments

Will59 Over a year ago

Can someone explain why this answer got negative rating? This looks fine? thanks.

Battery_Al Over a year ago

@Will59 It only works when inserting a single record, which is not usually the case when you're using insert into... on duplicate key

Collectives™ on Stack Overflow

Insert ... on duplicate key update nothing using MySQL

2 Answers 2

8 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related