I have pre-existing "batch" files of many mysql UPDATE commands, which are interspersed with commands which set temporary mysql variables using SET @vid:= ... ;
I would like to send a string containing many or all of these batched commands without having to do perl variable assignment in between and/or having to execute each UPDATE command individually. Better and more efficient to have the DB set the variables and execute all the commands at once than perform all the back-and-forthing in the other method.
As an example,
SET @tid:=(SELECT Team_ID FROM Teams WHERE Name='Thunder');
UPDATE Games SET Away_Team_ID=@tid WHERE Date='2012-05-29';
SET @tid:=(SELECT Team_ID FROM Teams WHERE Name='Spurs');
UPDATE Games SET Home_Team_ID=@tid WHERE Date='2012-05-29';
UPDATE ... ;
UPDATE ... ;
SET ... ;
Is there a way to put all the statements in the above code into a perl string and call prepare() and execute() on that string and have mysql do all the work? It is possible and easy using phpmyadmin -- just paste all of the statements into the SQL textarea and they are ALL executed in "batch" mode on Submit. Note that I am not looking for any record sets in return during this phase.
I hope this clears things up a bit and still expect that there is a solution. Thank you (again).