Updating lots of database fields in a single row

[Mark Fowler]

On Tuesday, January 22, 2013, Andrew Beverley wrote:

 is a better way of writing the following database query


With Perl there's always more than one way to do it. Greg's post rightly
recommended ORMs and pointed out that, indeed, DBIx::Class is commonly
considered the current best of breed for general use. Phil's post pointed
out how you can use Perl's syntax to easily create two lists, one for the
SQL and one for the binds.

A middle approach that isn't as heavyweight as an ORM but isn't as tedious
as rolling your own is to use a module to generate the SQL and Binds and
then pass them to DBI.

A good choice for this would be something like SQL::Abstract.

To create some SQL and binds from a hash you can just do this:

my $sa = SQL::Abstract->new;
my ($sql, @binds) = $sa->insert('table', {
  key => $value,
  key2 => $value2,
});
my $sth = $dbh->prepare($sql);
$sth->execute(@binds);

See http://goo.gl/Q8Huf for more info.

Mark
(Typed on my iOS doohicky very slowly)