CODEWITHSUNDEEP
mysql
user1216398
user1216398

Reputation: 1950

mysql duplicate key update issue

I have my index set to the field: testsuite_id and I'm executing the following query:

 INSERT INTO testsuite_dates (testsuite_id,start_date,end_date) 
 VALUES ('27798','2012:02:27 00:00:00','2012:02:28 00:00:00') 
 ON DUPLICATE KEY UPDATE testsuite_id='27798';

I would expect this to update the entry if an entry for testsuite_id exists or insert it if it doesn't but if I execute this query a second time it inserts a second entry with the same testsuite_id.

What am I missing?

Upvotes: 1

Views: 333

Answers (4)

Rizwan Mumtaz
Rizwan Mumtaz

Reputation: 3955

INSERT INTO testsuite_dates (testsuite_id,start_date,end_date) 
 VALUES ('27798','2012:02:27 00:00:00','2012:02:28 00:00:00') 
 ON DUPLICATE KEY UPDATE testsuite_id=testsuite_id+1;

Upvotes: 0

Marc B
Marc B

Reputation: 360612

The "on duplicate key update" only changes the fields YOU specify in the 'update' portion. You're updating only the key field, which triggered the constraint violation in the first place. The other fields are silently dropped, so you're effective doing update yourtable set id=id where id=xxx.

You have to list each field you want to be updated:

INSERT INTO testsuite_dates (testsuite_id,start_date,end_date) 
VALUES ('27798','2012:02:27 00:00:00','2012:02:28 00:00:00') 
ON DUPLICATE KEY UPDATE 
    start_date=values(start_date), end_date=values(end_date)

Note the use of the values() function in the update portion. This allows you to refer to the NEW value that would've been inserted, and use it for the update portion. This saves you having to embed the same data twice in the same query. Not a big deal for a few dates or numbers, but if you're inserting large blobs, it makes for a HUGE query string size savings.

Upvotes: 3

Churk
Churk

Reputation: 4627

did you set your testsuite_id to be a primary key, or have unique constraint

Upvotes: 0

AndreKR
AndreKR

Reputation: 33678

Then you don't have a PRIMARY or UNIQUE key on testsuite_id.

Upvotes: 4

Related Questions