-1

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.

ann
  • 576
  • 1
  • 10
  • 19
Copephobia
  • 1
  • the query you have there should work fine, but we might need to see more of your sql structure for a better answer. – Andy Groff Jan 16 '11 at 19:02

1 Answers1

0

I have a hard time understanding what you are trying to do, better explanation would be nice. From what I understand you are selecting data from tables in order to update other tables. Have you tried using an "UPDATE .. JOIN .." query? This should allow you to update multiple rows from one table based on associative data from another table.

For example

UPDATE user
JOIN game ON
    user.id=game.id_user
SET user.position=game.team1_position

Obviously this wont work with your code as I have very little info to go on, but that should give you an idea of what to go with.

Sorry if I'm totally off in understanding your problem, as said it's a bit hard to understand your exact issue based on what you've given us.

Naatan
  • 3,424
  • 4
  • 32
  • 51