Issue with generated primary keys when using INSERT ON DUPLICATE KEY UPDATE

Question

I'm developping an application in JSF 2.0, as DBMS I'm using MySQL. I'm having an issue with the INSERT ... ON DUPLICATE KEY UPDATE statement, it generates a new primary key every time a row gets updated. This is the query I'm using.

INSERT INTO aanwezigheid_i (jongereId, datum, aanwezig, aankomstTijd, vertrekTijd, alternatiefKamp, redenAfwezig)
VALUES (?,?,?,?,?,?,?)
ON DUPLICATE KEY
UPDATE aanwezig = ?, aankomstTijd = ?, vertrekTijd = ?, alternatiefKamp = ?, redenAfwezig = ?

The table 'aanwezigheid_i' has got the following columns:

aanwezigheidId (INT(11), PRIMARY KEY)| jongereId| datum| aanwezig| aankomstTijd| vertrekTijd| alternatiefKamp| redenAfwezig

If I insert a first row, the primary key 'aanwezigheidId' has got the value 1. If I UPDATE this first row 10 times and insert a second row afterwards, the primary key value of the second row is 12 and not 2. What am I doing wrong? Any help would be appreciated.

Can you post the output of SHOW CREATE TABLE aanwezigheid_i;? — N.B.
– N.B., Commented Jan 24, 2012 at 12:56

Eugen Rieck · Accepted Answer · 2012-01-24 12:56:50Z

2

If you use an AUTO_INCREMENT key, be aware, that calculating the new AUTO_INCREMENT value is done before attempting the INSERT. If the INSERT is converted into an UPDATE the new AUTO_INCREMENT value is lost.

This is by design, nothing you can do about it, apart from NOT using AUTO_INCREMENT

answered Jan 24, 2012 at 12:56

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

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

1 Comment

Bart1990 Over a year ago

Do you know how I can get the value of the primary key before the insert? I'll need its value to fill in the '?' for 'aanwezigheidId' in the INSERT statement.

voidengine · Accepted Answer · 2012-01-24 12:56:02Z

1

You're not sending aanwezigheidId - the primary key - into the INSERT, that's why a new row is created. The ON DUPLICATE KEY UPDATE clause only takes effect when a record with existing key is inserted.

answered Jan 24, 2012 at 12:56

voidengine

2,5791 gold badge18 silver badges31 bronze badges

Collectives™ on Stack Overflow

Issue with generated primary keys when using INSERT ON DUPLICATE KEY UPDATE

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related