Is it safe to update a MySQL table when repeating a result set using Perl DBI?

Question

In particular, I need to delete some rows from the table, iterating over all rows in the table. Does DBI have something like an updatable result set in Java? So, if I am something like:

$query_all = $dbh->prepare("select Id, X, Y, Z from MyTable"); $delete = $dbh->prepare("delete from MyTable where Id = ?"); $query_all->execute(); while ( my @res = $query_all->fetchrow_array() ){ my ($id, $x, $y, $z) = @res; # fetch the IDs of any other rows that have the same X, Y, Z values foreach ( the_duplicate_ids ){ $delete->execute($dup_id); # safe ?? # also delete from another table using $dup_id } } 

... its OK?

To give some context, I delete duplicate rows that have the same values ​​for columns X, Y, Z, and leaving only one row in each case (the first one found). If that was all I was doing, I would just set a unique index for these three columns to eliminate duplicates, but I also need to delete a row from another table for each duplicate deleted from MyTable .

In the end, I wrote a script to identify and collect all the identifiers for the rows that I need to delete into an array, and then iterate over this array, deleting the corresponding rows from both tables. However, I am still interested in finding the answer to the original question. If I get time, I will try to answer it myself, but if someone already knows that I would like to hear it.

Answer 1

Are you worried about a simultaneous modification error?

If you use transactions, it will not be a problem if you save the commit after you finish the iteration.

However, if you use autocommit, this can be a problem, so the solution would be:

Pseudocode ish - not verified

 #Loop through each row and note the records to delete my @deathrow = (); foreach my $row ( @resultset ) { push $row->{id}, @deathrow; } #Now that we're done iterating, do all the deletes in one statement $dbh->execute("DELETE WHERE id IN ( " . @deathrow . " )"); #something like that 

Answer 2

One possible solution might be something like the following snippet:

 my $dbh = DBI->connect("DBI:mysql:database=XXXX;host=localhost", "user", "pass", {'RaiseError' => 1, 'AutoCommit' => 0}); my $query_all = $dbh->prepare("select Id, X, Y, Z from MyTable"); eval { my $delete = $dbh->prepare("delete from MyTable where Id = ?"); $query_all->execute(); while ( my @res = $query_all->fetchrow_array() ){ my ($id, $x, $y, $z) = @res; # fetch the IDs of any other rows that have the same X, Y, Z values foreach my $dup_id (@the_duplicate_ids){ $delete->execute($dup_id); # safe ?? # also delete from another table using $dup_id } } $dbh->commit(); }; if ( $@ ) { print "Transaction rollback: $@ "; $dbh->rollback(); }