CODEWITHSUNDEEP
mysqlsql
Bart1990
Bart1990

Reputation: 255

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

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.

Upvotes: 0

Views: 228

Answers (2)

Eugen Rieck
Eugen Rieck

Reputation: 65274

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

Upvotes: 2

voidengine
voidengine

Reputation: 2579

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.

Upvotes: 1

Related Questions