Inserting only once

Question

I have 2 instances of a program, accessing 1 table in a database.
Table points

game    |  player1  |  player2
--------|-----------|-----------
Poker   |    5      |   
Skat    |    8      |    4
Bridge  |           |   10

If player1, using the first instance, wants to save his points (e.g. Poker) into the table, it has to be made sure, whether Poker is already there. If not, Poker will be inserted together with the points of player1. But if Poker was already inserted by player2, only the points of player1 should be inserted (the row should only be altered).

How can I make sure, that every sort of game is only inserted once and in this case only the points are inserted (altered)?
I think, the column game has to be unique.

My solution is to query the sort of game at first and check the number of rows. If there are 0 rows, insert the game and points, if there is already 1 row, only insert the points.
But is there a better/faster solution, whithout querying at first?

Maybe you should reconsider your database design. If you happen to add more users it would be better to have tables for `users`, `games` and a crossreferencing table `user_game`. — dgw, Apr 11 '12 at 21:32

score 4 · Accepted Answer · answered Apr 11 '12 at 21:28

4

INSERT INTO points (game,player1) VALUES ('Poker',5)
    ON DUPLICATE KEY UPDATE player1=5;

For more information, see the MySQL documentation: INSERT ... ON DUPLICATE KEY Syntax.

answered Apr 11 '12 at 21:28

nosid

48,932
13
112
139

Is it also possible for a **combination** of entries? So that `Poker` is allowed several times, but only once for every player? The combination `game - player1` only should be unique. – Evgenij Reznik Apr 11 '12 at 22:11

score 0 · Answer 2 · answered Apr 11 '12 at 21:24

Break your insert into two statements. For this to work, 'game' must be a unique column - that is, no two values can be the same.

INSERT INTO 'game' VALUES('Poker',null,null);
UPDATE 'game' SET player1='5' WHERE 'game'='POKER';

The first line won't affect anything if it's already there, because the unique constraint will prevent it.

score 0 · Answer 3 · edited May 23 '17 at 12:11

0

Use the replace into syntax. Note that you can create a unique constraint for your database tables.

Read about replace into here. Read about unique constraint here.

edited May 23 '17 at 12:11

Community

1
1

answered Apr 11 '12 at 21:32

Lajos Arpad

64,414
37
100
175

score 0 · Answer 4 · answered Apr 11 '12 at 21:39

0

 REPLACE INTO points (game,player1) VALUES ('Poker',5);

answered Apr 11 '12 at 21:39

kasavbere

5,873
14
49
72

Inserting only once

4 Answers4