Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

INSERT INTO table1
  (title, abr, name) 
  VALUES
  ('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 2

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:

INSERT INTO table1
(`title`, `abr`, `name`) 
VALUES
('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
1  
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
WHERE   title STARTS WITH ?

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

 
discard

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.