Mysql – Resolving DUPLICATE for a column with UNIQUE

duplicationinnodbMySQLunique-constraint

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?

Best Answer

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.

Related Question