Take the tour ×

I have a few column with UNIQUE INDEX. In some queries, duplicates should be acceptable, in which a postfix should be added to the value. For example, if test title exits, we change it to test title-2.

Is there a way to make a unique value upon DUPLICATE?

  (title, abr, name) 
  ('title', 'abr', 'name')

In this typical example, all three columns are UNIQUE. When the INSERT fails, I do not know which col caused the error to change its corresponding value.

How can I make a query to change value to value-identifier upon duplicate error?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

I would agree with @BillThor that what you want to do may create some problems but what you need is ON DUPLICATE KEY UPDATE statement so your query would be something like this:

(`title`, `abr`, `name`) 
ON DUPLICATE KEY UPDATE `title` = CONCAT(`title`, '-2');

You can change CONCAT to any other function or your own stored procedure.

share|improve this answer
How should I know that the duplicate is on the column title? it can be from columns abr or name, which are also UNIQUE. –  All Nov 26 '12 at 21:43
Use stored procedure which will check if given value already exists and in such case will return updated value so it will be something like ... ON DUPLICATE KEY UPDATE title = myTitleChecker(VALUES(title)), abr = myAbrChecker(VALUES(abr)), myNameChecker(VALUES(name)); –  onlineapplab.com Nov 26 '12 at 22:13
now it seems to be a practical solution! –  All Nov 27 '12 at 0:23
add comment

I would look at your model first. Placing a unique key constraint on a non-unique column gets you into this kind of problem. What happens when you get a legitimate value like test title-2 but you've already used that value to resolve a collision on test title.

If I had to resolve your problem, I would build a query for each unique key of the form:

SELECT  title
FROM    table1

The results will need to be parsed to see what the suffix should be. It might be possible to do this in a stored procedure. However, I would strongly argue that the data model needed to be fixed, not the code.

share|improve this answer
add comment

Your Answer


