CODEWITHSUNDEEP
javamysql
Evgenij Reznik
Evgenij Reznik

Reputation: 18614

Inserting only once

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?

Upvotes: 0

Views: 155

Answers (4)

kasavbere
kasavbere

Reputation: 6003

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

Upvotes: 0

Lajos Arpad
Lajos Arpad

Reputation: 77012

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.

Upvotes: 0

nosid
nosid

Reputation: 50124

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.

Upvotes: 4

Nathaniel Ford
Nathaniel Ford

Reputation: 21249

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.

Upvotes: 0

Related Questions