Perl: Update Multiple Rows with one MySQL Call

Question

Seems that it may not be possible, but hey I might as well ask, I could be wrong. Was wondering if there's anyway for perl to update multiple rows using one MySQL call, I'm using DBI.

Any help or feedback would be greatly appreciated, this is possible in MSSQL through ASP and ASP.net so was wondering if also possible through perl on MySQL.

Thank you for your feedback!

Answer 1

First and most important, you absolutely should not interpolate variables directly into your SQL strings. That leaves open the possibility of SQL injection attacks. Even if those variables don't come from user input, it leaves open the possibility of dangerous bugs that can screw up your data.

The MySQL DBD driver does support multiple statements, though it's turned off by default as a safety feature. See mysql_multi_statements under the Class Methods section in the DBD::mysql documentation.

But a much better solution, which solves both problems at once and is more portable, is to use prepared statements and placeholder values.

my $sth = $dbh->prepare("UPDATE LOW_PRIORITY TableName SET E1=?,F1=? WHERE X=?");

Then, get your data in a loop of some sort:

while( $whatever) { 
    my ( $EC, $MR, $EM ) = get_the_data();
    $sth->execute( $EC, $MR, $EM );
}

You only need to prepare the statement once, and the placeholder values are replaced (and guaranteed to be properly quoted) by the DBD driver.

Read more about placeholders in the DBI docs.

Answer 2

You don't need mysql_multi_statements, as friedo suggests.

You need turn off AutoCommit mode before you call the loop containing your UPDATE command:

**$dbh->{AutoCommit} = 0;**
while( $condition ) {
   my $myParam = something();
   ...
   $sth->execute( $myParam ); #your prepared UPDATE statement
   ...
}
**$dbh->commit();**