Mysql change values to insert on duplicate key

Question

Is there a way to do it? Considering I have the following table:

 id | value
----|------
 1  | A
 2  | B

If I try to insert the values (1, "C") I get Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY' as expected, considering id is a unique primary key. So, I tried using the following insert inside an AFTER INSERT ON trigger with the following insert syntax:

INSERT INTO table VALUES (NEW.id, NEW.value)
ON DUPLICATE KEY UPDATE NEW.id = NEW.id + 5000;

It removes the duplicate key error, but it just won't insert the duplicate one(s).

I made a procedure following Yogi's suggestion. This is what it looks like:

DELIMITER //
CREATE PROCEDURE set_keys(IN id INT, IN value char(1))
BEGIN
    DECLARE CONTINUE HANDLER FOR 1062
    SELECT concat('Duplicate keys detected: ',id,', ',value) AS msg;

    INSERT INTO tests (id, value) VALUES(id + 5000, value);
END;
//

Is doing CALL set_keys(NEW.id, NEW.value); from now on the proper way to do it?

isn't simpler to use an auto-increment key? P.s. consider that using ON DUPLICATE KEY UPDATE you don't insert the new row, simply edit the old one, so you 1-A becomes 5001-A and 1-C is lost — Roberto Bisello
– Roberto Bisello, Commented Oct 19, 2017 at 10:55
@RobertoBisello It would, but I'm importing data from another database, and can't modify the original one except for inserts. If the other database gives me 1-C, I should be able to insert it and keep both values from the column value. — Newwt
– Newwt, Commented Oct 19, 2017 at 11:00

Yogi · Accepted Answer · 2017-10-19 11:47:55Z

1

Rather than directly performing INSERT INTO table,
You can perform this using PROCEDURE/FUNCTION.

Do exception handling for DUPLICATE KEY error under EXCEPTION block, increment the KEY column and RE_RUN the INSERT Statement again

answered Oct 19, 2017 at 11:47

Yogi

1,92515 silver badges24 bronze badges

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

5 Comments

Newwt Over a year ago

Would making a function that does somethink like select count(*) from table where id = @id_to_insert and make an "if count = 1, add 5000" work? I'm sorry, I just don't know that much about mysql :/

Yogi Over a year ago

@Newwt -- Only problem i see with your answer is, before every insert i will run one extra query, where as with exception handling, i will run extra query for duplicate records only...

Newwt Over a year ago

I informed myself a little and -I think- I did what you suggested. Do you mind checking my edit on the question to see if I did it right? Thanks for the help so far!

Yogi Over a year ago

@Newwt- Yes, Seems Correct to me. You need CONTINUE HANDLER, which you are using

Newwt Over a year ago

I noticed just now if for example I try to insert an id of 1 without anything on the table, it inserts it directly as id + 5000, without there being a duplicate. What is causing it?

Collectives™ on Stack Overflow

Mysql change values to insert on duplicate key

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related