Update Multiple Rows?

I have a form that contains 16 players. Each of those players is given a position from a drop down box. I need to update my table which contains the players id number with the position they have been assigned in the form. I know how to do this with multiple sql statements - UPDATE tblteamsheet SET teamsheetpos = 'LB' WHERE PlayerID = '6'. UPDATE tblteamsheet SET teamsheetpos = 'GK' WHERE PlayerID = '10'.
Is it possible for me to run these statements together and only query the DB once and update all the records or do I have to run multiple queries?
Thanks in advance
Arnold

Hmm... an interesting proposition.

I don't believe that you can do multiple updates through SQL like that, but you can update from another table.

Basically, it works like this: You create a temporary table that contains the key of the record you want to update and the value of the newly updated field. So for positions,

CREATE TABLE tmpTable (PlayerID INT(11) NOT NULL, Position VARCHAR(50) NOT NULL);

or something like that.

Then when you grab the data back from your form, you can multiple insert it into tmpTable, i.e.:

INSERT INTO tmpTable(PlayerID, Position) VALUES (1,"Outfield"), (2,"Shortstop");

etc.

Then, you can do an update based on the info in this tmp table:

UPDATE players, tmpTable SET players.position = tmpTable.Position WHERE players.ID=tmpTable.PlayerID;

Provided that players is the table you want to update, ID is the players name for the key column, and players.position is what you want to update.

And don't forget to delete your tmpTable

DROP TABLE tmpTable;

Or next time you'll get some weird data corruption.

So that's four queries instead of 16. :-D

(there may be a better way to do it using PHP built-in functions, but thats the quick SQL work around for database people like me. *grin*)

Ok it all works until I get to the UPDATE command - I don't think that you can use a Join in an update command - I may be wrong but when I try it I get an error. My Code is:


$sql = "SELECT tblplayers.PlayerID FROM tblplayers WHERE tblplayers.ClubID = '$ClubID' ORDER BY tblplayers.PlayerID ASC";
$RS = $conn->Execute($sql);

$i = 0;

WHILE (!$RS->EOF){
$player[$i] = $RS->Fields("PlayerID");
$teamsheetpos[$i] = $HTTP_POST_VARS["$player[$i]pos"];
$RS->Movenext();
$i++;
}

$sql = "CREATE TABLE tmpTable (PlayerID INT(11) NOT NULL, Teamsheetpos VARCHAR(10) NOT NULL)";
$conn->Execute($sql);



FOR ($k=0; $k<$i; $k++){

IF (($k+1) == $i){ //this removes the comma from the last entry
$sqlvalues = $sqlvalues . "($player[$k],'$teamsheetpos[$k]')";
}ELSE{
$sqlvalues = $sqlvalues . "($player[$k],'$teamsheetpos[$k]'),";
}
}
$sql = "INSERT INTO tmpTable(PlayerID, Teamsheetpos) VALUES $sqlvalues";
$conn->Execute($sql);

$sql = "UPDATE tblteamsheet, tmpTable SET tblteamsheet.Teamsheetpos = tmpTable.Teamsheetpos WHERE tblteamsheet.PlayerID = tmpTable.PlayerID";
$conn->Execute($sql);

//$sql = "DROP TABLE tmpTable";
//$conn->Execute($sql);

Apparently you can only do this with version 4.0.2 which I don't have