Updating lots of database fields in a single row

[Abigail]

On Tue, Jan 22, 2013 at 10:57:29PM +0000, Andrew Beverley wrote:
> I've not been developing with Perl for long, so I'd like to know if
> there is a better way of writing the following database query (or is
> there a better place to ask?):
> 
> 
> my @fields = qw(field1 field2 field3 field4 field5 field6 field7 ... );
> my @updates;
> foreach my $field (@fields)
> {
>     push @updates, "$field = '$hash->{$field}'" if $hash->{$field};
> }
> my $values = join ',', @updates;
> my $sth = $self->dbh->prepare("UPDATE table SET $values WHERE id = ?");
> $sth->execute($opdefs_id);
> 
> 
> Basically, I'd like to update lots of fields in a single database row.
> Obviously I could write out each updated field individually, but I
> wondered whether the above is considered tidier, or whether there is a
> better way altogether? The problem with the above code is that I'm not
> using placeholders and bind values (which I assume is to be preferred)
> so I'll also need to escape values as required.
> 


I'd write something like (untested):

my @fields = grep {$$hash {$_}} qw [field1 field2 field3 ...];
if (@fields) {
    my $query = do {local $" = ", ", <<"    --"};
    UPDATE  table
       SET  @{[map {"$_ = ?"} @fields]}
     WHERE  id = ?
    --
    my $dbh = $sth -> dbh;
    eval {
        local $dbh -> {AutoCommit} = 0;
        local $dbh -> {RaiseError} = 1;
        local $dbh -> {PrintError} = 0;
        my $sth = $dbh -> prepare ($query);
        $sth -> execute (@{$hash} {@fields}, $opdefs_id);
        $dbh -> commit;
        1;
    }
    or do {
        my $error = $@;
        eval {
            $dbh -> rollback;
            1;
        } or die "Rollback failed: $@ (after error: $error)\n";
        die $error, "\n";
    }
}


Abigail