Mysql insert, if duplicate value on unique key, insert a different value

Question

Is it possible, when inserting a row with unique field, to change the value that goes into unique field into something different in case of duplicate?

Situation: a user is adding a new article, which name cannot be duplicate. For some reason, the user types the name that already exists in the database.

If using "ON DUPLICATE KEY UPDATE" - previous record (with the same name as inserted) gets all new data in other fields (update occurs) - not good.

What is required is, on insert, if the field is unique, and there is a match in DB, change the value being inserted into something like "[DUPLICATE] name".

This will reveal the presence of duplicate and the user will just change the name (which, likely, was inserted as duplicate by error).

Try the insert. If it fails, change the unique value and hit it again. — tadman
– tadman, Commented Aug 16, 2016 at 16:51
Is there a way to include intermediary (select... where name = "article name") into insert statement, so the select would produce either true (i.e. concatenate name with "[duplicate]" string) or false (the name is inserted as is)? — bbe
– bbe, Commented Aug 17, 2016 at 10:00
INSERT INTO and SELECT is not possible due to mysql restriction (mysql docs: "you cannot insert into a table and select from the same table in a subquery"). Working solution at this moment: - go over all field names that are unique (known in advance); - if duplicate present in DB (i.e. count !=zero) - add timestamp to the value to be inserted/updated. A modified form of timestamp is used instead of a constant value to avoid making another duplicate, which name (i.e. 'name[dub]') is already present in DB from previous add/edit. — bbe
– bbe, Commented Aug 18, 2016 at 17:37

twodee · Accepted Answer · 2016-08-22 03:57:12Z

1

You have to do something like (using prepared statements with mysqli)-

 SELECT name FROM table WHERE name=?

Bind the variable (I am assuming $articleName) and store the result, then check if there is a match with mysqli_num_rows().

If there exists one or more rows, rename the article name.

 $articleName=$articleName."[DUPLICATE]";

Then insert into the db.

Note: There will usually be no same article names, because a sentence can be constructed in a many ways. Consider building a very smart program to detect that or manually check like they do in SO.

edited Aug 22, 2016 at 3:57

answered Aug 16, 2016 at 16:57

twodee

6065 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql insert, if duplicate value on unique key, insert a different value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related