Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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`) 
('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)); – Nov 26 '12 at 22:13
now it seems to be a practical solution! – All Nov 27 '12 at 0:23

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.