Updating lots of database fields in a single row

[Jérôme Étévé]

On 22 January 2013 22:57, Andrew Beverley <andy at andybev.com> 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

> 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);
>

ORM solutions set aside, yep it's faster to update the whole row in
one go (unless your query becomes so big it exceeds your db query size
limit, specially if some of your fields are very long).

Something critical is missing in your code though: quoting:

Replace $field = '$hash->{$field}' with " $field =".$dbh->quote($hash->{$field})

The DBI quote method will 'do the right thing to avoid screwing up
your queries'. http://search.cpan.org/dist/DBI/DBI.pm#quote

I'm quite surprised no one mentionned it (except indirectly via
bindings). Probably an effect on over reliance on ORMs :)

J.


-- 
Jerome Eteve.

jerome.eteve at gmail.com