CODEWITHSUNDEEP
mysql
Lion
Lion

Reputation: 15

On duplicate key issue

I have a table for updating times for a game, in which columns are unique to avoid duplicates.

My problem is the following: how to update a particular column only and avoid inserting a new row if they are the same?

Here is the query.

I did this code and it still enters a new row instead of updating the Time Column only:

INSERT INTO `leaderboard` (`Persona`, `Level`, `Track`, `Car`, `Mode`, `Time`, 
                           `Date`, `Timestamp`, `HMS`) 
                           VALUES ('STIG', 80, 'SPA FRANCORCHAMPS', 
                                  'AUDI R8 LMS ULTRA', 'HD', '02:17.332',
                                  '2014-12-06', '1417825487', '1:24:47')
ON DUPLICATE KEY UPDATE `Time` = '02:17.332';

Upvotes: 1

Views: 67

Answers (1)

Mathieu Rodic
Mathieu Rodic

Reputation: 6762

You are actually creating a composite unique key in:

ALTER TABLE `leaderboard`
ADD UNIQUE KEY `Persona` (`Persona`,`Level`,`Track`,`Car`,`Mode`,`Time`,`Date`,`Timestamp`,`HMS`);

It means that the tuple (Persona, Level, Track, Car, Mode, Time, Date, Timestamp, HMS) will be unique. However, the time columns are to be updated, and it is very likely they won't be unique.

Maybe what you want to do is:

ALTER TABLE `leaderboard`
ADD UNIQUE KEY `Persona` (`Persona`,`Level`,`Track`,`Mode`);

Upvotes: 1

Related Questions