Re^3: SQL: Update column(s) value with extra WHERE

Granted. I was focused more on eliminating the tricky code. Specifically:

Absolutely correct. Prepare is expensive. The thing is, both the workings of SQL::Abstract and the OP's logic seem to encourage a prepare for each execute. If I were implementing I would ditch SQL::Abstract in favor of straight DBI. But for better or worse I decided to restrict my response to the original question.
Equally correct. I was careless with transaction scoping. Thanks.
Your preferred error handling is also mine: turn off PrintError, turn off RaiseError, and write simpler code. Again, for better or worse I decided to focus on the original question.

Thanks.

wyant

Comment on Re^3: SQL: Update column(s) value with extra WHERE

Replies are listed 'Best First'.
Re^4: SQL: Update column(s) value with extra WHERE by Marshall (Canon) on Jul 17, 2023 at 09:11 UTC
Hi Wyant, I think "turn off RaiseError" is a typo. The default for both PrintError and RaiseError is off (0). RaiseError=1 essentially means "report errors by dying". This is my standard open DB code: `my %attr = ( RaiseError => 1); #auto die with error printout my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",\%attr) or die "Couldn't connect to database $dbfile: " . DBI->errstr;` [download] Yes, the main thing about the OP's post is that updating is too complicated. My code below: `$dbh->begin_work; foreach my $row_hash_ref (@$result){ $exe->($row_hash_ref,$col_arrayref); $update->execute(@$row_hash_ref{@$col_arrayref},$row_hash_ref->{rowi +d}); } $dbh->commit;` [download] For each row that was selected with the WHERE clause, run the user-supplied function, then use the pre-prepared SQL update statement. If an error happens during an update, this will happen before the commit and the result will be that no updates at all are done. "Like it never even happened". This code will run very quickly. Update: The update being done in the question has the property that running it multiple times is harmless. That is a very nice property to have. Maybe you have an update without that property and you want to save a copy of the DB. I would use the DB to do that via standard SQL instead of Perl code. You could have a regular table or a temp in-memory table for that purpose. In general, once you have a DB, push as much work as possible onto it (like sorting, copying, etc.)	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^4: SQL: Update column(s) value with extra WHERE
by Marshall (Canon) on Jul 17, 2023 at 09:11 UTC

I think "turn off RaiseError" is a typo. The default for both PrintError and RaiseError is off (0). RaiseError=1 essentially means "report errors by dying". This is my standard open DB code:

my %attr = ( RaiseError => 1);  #auto die with error printout
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",\%attr)
    or die "Couldn't connect to database $dbfile: " . DBI->errstr;
[download]

$dbh->begin_work;
foreach my $row_hash_ref (@$result){
  $exe->($row_hash_ref,$col_arrayref);
  $update->execute(@$row_hash_ref{@$col_arrayref},$row_hash_ref->{rowi
+d});
}  
$dbh->commit;
[download]

Update: The update being done in the question has the property that running it multiple times is harmless. That is a very nice property to have. Maybe you have an update without that property and you want to save a copy of the DB. I would use the DB to do that via standard SQL instead of Perl code. You could have a regular table or a temp in-memory table for that purpose. In general, once you have a DB, push as much work as possible onto it (like sorting, copying, etc.)

[reply]
[d/l]
[select]