Perl: update multiple rows with a single MySQL call

Question

It seems that this may not be possible, but I can also ask if I could be wrong. Interestingly, if in any case for perl to update multiple rows using a single MySQL call, I use DBI.

Any help or feedback is appreciated, this is possible in MSSQL via ASP and ASP.net, so I was wondering if it is also possible through perl in MySQL.

Thank you for your feedback!

Answer 1

First and foremost, you absolutely must not interpolate variables directly into your SQL strings. This leaves open the possibility of SQL injection injections. Even if these variables do not come from user input, this leaves open the possibility of dangerous errors that can ruin your data.

The MySQL DBD driver supports several statements, although it is disabled by default as a security feature. See mysql_multi_statements in the Class Methods section of the DBD :: mysql documentation.

But a much better solution that simultaneously solves both problems 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 the data in some kind of loop:

 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 correctly specified) by the DBD driver.

Read more about placeholders in DBI docs .

Answer 2

You do not need mysql_multi_statements, as friedo suggests.

You need to disable AutoCommit mode before you invoke the loop containing the UPDATE command:

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