multiple updates

[shaddf]

i have this table structure and an array of new records, how can i run an update on all player ids with new ones for a particular matchid

select  player_id, MatchID,minutes from soka_player_statistics_tbl;
+-----------+---------+---------+
| player_id | MatchID | minutes |
+-----------+---------+---------+
|         1 |      29 |    NULL |
|         2 |      29 |    NULL |
|         3 |      29 |    NULL |
|         4 |      29 |    NULL |
|         5 |      29 |    NULL |
|         6 |      29 |    NULL |
|         7 |      29 |    NULL |
|         8 |      29 |    NULL |
|         9 |      29 |    NULL |
+-----------+---------+---------+

I have tried this :

 update soka_player_statistics_tbl set player_id=inPlayerId where MatchID=inMatchId;

but if i run  in an array from php to update matchID 29 with new player ids,i get an

ERRNO: 256
TEXT: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-29' for key 'PRIMARY'

[NigelRel3]

This is not a way to do it.  I'm not sure of exactly what your reasons for doing what you are doing.

 

The problem is that in your statement you are going to try and update every record for a particular matchid to (for example) have a playerid of 1.  What you probably want to do is just have a list of player ID's and want to make sure that your set of records match.  Update isn't the way to do it.  It would probably be better and a lot easier to delete the existing records and insert new records.

[shaddf]

This is not a way to do it.  I'm not sure of exactly what your reasons for doing what you are doing.

 

The problem is that in your statement you are going to try and update every record for a particular matchid to (for example) have a playerid of 1.  What you probably want to do is just have a list of player ID's and want to make sure that your set of records match.  Update isn't the way to do it.  It would probably be better and a lot easier to delete the existing records and insert new records.

I thought about that, but what about the scenario were  I would like to  update  only the line up i.e the player id for a particular game(it could be many player ids and not one).And  a new list of player names has been selected.I would not want to re enter their statistics afresh but only for the changed player ids in order to reset the lineup .look at this table:

select  player_id, MatchID,minutes,assists from soka_player_statistics_tbl;
+-----------+---------+---------+---------+
| player_id | MatchID | minutes | assists |
+-----------+---------+---------+---------+
|         1 |      29 |    NULL |    NULL |
|         2 |      29 |    NULL |    NULL |
|         3 |      29 |    NULL |    NULL |
|         4 |      29 |    NULL |    NULL |
|         5 |      29 |    NULL |    NULL |
|         6 |      29 |    NULL |    NULL |
|         8 |      29 |    NULL |    NULL |
|         9 |      29 |    NULL |    NULL |
+-----------+---------+---------+---------+

I would like to be updating only the player ids without changing the other data.

Is there a way I can use case statement here?

Edited March 17, 2017 by shaddf

[NigelRel3]

You would have to identify just the rows that have changed and update them, so it means finding the players that have been changed and using the match id and old player id to update that specific row to have the new player id.

[shaddf]

You would have to identify just the rows that have changed and update them, so it means finding the players that have been changed and using the match id and old player id to update that specific row to have the new player id.

what if all have been changed?how can i   adress thascenariot?