Updating multiple rows with an array
- by Copephobia
I have a table that holds user information. One of the columns holds the position of the user in the game they are in.
When a game is being created, I need to update the positions of the users of each team.
Here is an example:
Game id : 7
Team 1 users : 1,2
Team 2 users : 3,4
team1_position : array(1,2)
team2_position : array(13,14)
What I want to do is update the user table using the array of positions in the SET area.
My goal is to be able to update the users without the need for their id (I have different size game boards, so I have multiple position arrays for each board size)
How can I do something like this:
UPDATE user SET position='(team1_position)' WHERE game = '7' AND team = '1'
I feel like it would be a waste of resources to select all the id's of each team and update them separately.
