13

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?

Teun Zengerink
  • 4,277
  • 5
  • 30
  • 32
Tudor
  • 1,133
  • 1
  • 12
  • 28
  • 1
    Why are stored procedures not an option? – juergen d Oct 23 '12 at 23:51
  • and why on duplicate key might update a key with a new value? – John Woo Oct 23 '12 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 Oct 23 '12 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 Oct 23 '12 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 Oct 23 '12 at 23:59
  • @TudorTudor See my answer - you can just set a column value to the existing value, foreign key contraints will bubble up unlike `IGNORE` but no values will change on conflict. – doublesharp Oct 24 '12 at 00:00

2 Answers2

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.

doublesharp
  • 26,888
  • 6
  • 52
  • 73
  • 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 – Tudor Oct 24 '12 at 00:01
  • 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. – doublesharp Oct 24 '12 at 00:03
  • yea was thinking of adding an extra null attribute, but thought there might be another solution. Hate this mysql limitations. – Tudor Oct 24 '12 at 00:05
  • To be fair, this kind of logic *is* supported by MySQL, you just have to use a stored procedure. – doublesharp Oct 24 '12 at 00:11
  • 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 Oct 24 '12 at 00:18
  • I'm saying that because once i had to implement some oo concepts into mysql triggers . It was a nightmere . – Tudor Oct 24 '12 at 00:19
  • In that case you could always run a `SELECT` first to check for duplicate data, and then follow with the `INSERT`. Not as efficient, but it keeps the logic in your app. – doublesharp Oct 24 '12 at 00:20
  • Please add the comment answeres to the final answer so i can validate it. – Tudor Oct 24 '12 at 00:29
-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;
Tudor
  • 1,133
  • 1
  • 12
  • 28
  • Can someone explain why this answer got negative rating? This looks fine? thanks. – Will59 Jul 29 '20 at 14:24
  • @Will59 It only works when inserting a single record, which is not usually the case when you're using `insert into... on duplicate key` – Battery_Al Aug 14 '20 at 18:26