Duplicate Key in SQL

With 'ON DUPLICATE KEY' you can specify the behavior when an entry with an already existing primary key (in your case probably uuid [and maybe name]) is inserted into the table. The syntax is basically the same as for UPDATE but without the selecting part:
Normal UPDATE statement:

Code (Text):

UPDATE stats SET combatpoints=combatpoints+1 WHERE uuid=<uuid>;

Remove the redundant stuff: UPDATE stats SET combatpoints=combatpoints+1 WHERE uuid=<uuid>

Combined with INSERT and ON DUPLICATE KEY:

Code (Text):

INSERT INTO stats(uuid, name, combatpoints, ip) VALUES ('%s', '%s', %s, '%s')
  ON DUPLICATE KEY UPDATE combatpoints=combatpoints+1

 

Yep, that would be one way to do it.

Alternatively you could

1. before inserting, check if the player already exists in the database
   
2. handle the error that MySql returns (#1062 - Duplicate entry '...' for key 'PRIMARY') when trying to insert with a duplicate key
   
3. use INSERT IGNORE to suppress the error
4. (as you said) do something trivial with ON DUPLICATE KEY; e.g. ON DUPLICATE KEY UPDATE uuid=uuid

 

Just felt like commenting that you could always do a check for if the table contains a value and use either UPDATE or INSERT based on that. Pretty sure it's been mentioned, but why not say it again?

 

Frankly, the only efficient way of doing this would either be ON DUPLICATE KEY UPDATE or INSERT IGNORE (both INSERT + error check and SELECT + INSERT/UPDATE might require 2 queries, which makes it an inefficient approach - on top of the race condition SELECt + INSERT/UPDATE introduces).

As for INSERT IGNORE, it is not a recommended approach since it'll silence any error that occurs, not just duplicate entry errors.

Spoiler: Race condition

Code (Text):

Server A: SELECT * FROM table WHERE id = 1
Server B: SELECT * FROM table WHERE id = 1
// Both servers get no results
Server A: INSERT INTO table VALUES('bar')
Server B: INSERT INTO table VALUES('bar')
// Server B now receives a duplicate entry error