MySQL - Different UPDATE else INSERT statement

Question

I am busy doing an UPDATE/INSERT request, but here is the crux:

table PLAYERS {
    player_id
    game_id
    team_id
    position
    number
}

What is supposed to happen is the following:

I test if there is an entry where player_id = '$player_id' AND game_id = '$game_id' AND team_id = '$team_id'.

If there is, then the following happens:

position = '$position' AND number = '$number'

Is there any way I can do this using just MySQL query language, without the need for PHP validation between queries?

I don't have a PRIMARY KEY for this table, but have INDEXES on game_id, player_id, and team_id, if that helps... — Anriëtte Myburgh
– Anriëtte Myburgh, Commented Aug 20, 2009 at 22:35
You can create a compound Primary Key across the game, player and team fields. — Toby Hede
– Toby Hede, Commented Aug 20, 2009 at 22:52

Tyler Carter · Accepted Answer · 2009-08-20 22:07:26Z

6

INSERT INTO TABLE (COLUMNS) VALUES(FIELDS) UPDATE ON DUPLICATE KEY position = somevalue, number=number

1) Tries to Insert
2) If there is a record with a unique field (Primary Key, Unique Index, etc) update that field instead.

answered Aug 20, 2009 at 22:07

Tyler Carter

61.7k22 gold badges133 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Anriëtte Myburgh Over a year ago

This is my query:

INSERT INTO players (game_id,player_id,position,number) VALUES('$game_id','$players[$i]','$position[$i]','$number') UPDATE ON DUPLICATE KEY position = '$position[$i]', number='$number'

But I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE ON DUPLICATE KEY position = 'LH Prop', number='1'' at line 1

Any ideas?

Anriëtte Myburgh Over a year ago

I have been inserting like this the whole time, and it worked.

Zed Over a year ago

I think it should be "ON DUPLICATE KEY UPDATE"

Anriëtte Myburgh Over a year ago

Yes, thanks Zed, I changed it to that and then it processed, but I think my problem is the fact that I don't have any PRIMARY KEY, except INDEXES on game_id, player_id and team_id. So it processes the request, but keeps on inserting new entries, but in fact, the new entries are precise duplicates of other entries in the table. Anyone else have some direction for me?

searlea Over a year ago

ALTER TABLE PLAYERS ADD PRIMARY KEY (player_id, game_id, team_id);

|

Anriëtte Myburgh · Accepted Answer · 2009-09-14 08:21:15Z

0

Well, I fixed this with multiple IF-statements to determine whether to insert or to update.

answered Sep 14, 2009 at 8:21

Anriëtte Myburgh

13.5k11 gold badges54 silver badges72 bronze badges

Comments

HaveAGuess · Accepted Answer · 2009-09-18 13:34:57Z

0

That will not work unless its in a transaction, or you can guarantee that simultaneous requests against the DB will not be run

answered Sep 18, 2009 at 13:34

HaveAGuess

1,2411 gold badge13 silver badges23 bronze badges

Collectives™ on Stack Overflow

MySQL - Different UPDATE else INSERT statement

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related