Jump to content
Sign in to follow this  
shaddf

multiple updates

Recommended Posts

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'

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by shaddf

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.