Updating lots of database fields in a single row

[Greg McCarroll]

In this day and age I'd be looking at an ORM[1] layer for such simple changes, they are almost foolproof until someone is a fool ;-). And they will probably avoid stupid SQL mistakes that you and I might both make.

And DBIx::Class[2] is the current best of breed, it can also 'reverse engineer'[3] your existing schema to Perl modules.

G.

[1] http://en.wikipedia.org/wiki/Object-relational_mapping
[2] http://search.cpan.org/~getty/DBIx-Class-0.08204/lib/DBIx/Class.pm
[3] http://search.cpan.org/~getty/DBIx-Class-0.08204/lib/DBIx/Class/Manual/Intro.pod#Using_DBIx::Class::Schema::Loader

On 22 Jan 2013, at 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.
> 
> Thanks,
> 
> Andy
> 
>