Updating lots of database fields in a single row

[William Blunn]

On 22/01/2013 22:57, 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.

You should never[1] insert variable values into the query. Instead you 
should always use placeholders and binding.

Re-writing your code snippet to use placeholders and binding (and fixing 
the edge case where no fields need updating) gives:

my @fields = qw(field1 field2 field3 field4 field5 field6 field7 ... );

my @fields_to_update = grep { $hash->{$_} } @fields;

if (@fields_to_update) {
     my $query =
         'UPDATE table SET '
         . join(',', map { "$_ = ?" } @fields_to_update)
         . ' WHERE id = ?';

     my $sth = $self->dbh->prepare($query);

     # Be sure to have opened your DBI with RaiseError => 1 earlier
     $sth->execute( @$hash{ @fields_to_update }, $opdefs_id );
}

However the fashionable way to do database operations in Perl is to use 
an ORM such as DBIx::Class.

ORMs have some advantages, and a lot of people swear by them, though 
they are not without their disadvantages.

This sort of question could be asked on stackoverflow.com.

Regards,

Bill

[1] (There are occasional cases where there is merit in inserting a 
value directly into the query. But this is something of an advanced 
technique to be used in those cases where the benefits outweigh the 
risks. Beginners would be best advised to stick firmly to placeholders 
and binding.)