Advice or best practice for updating multiple rows with a single query

Dear Mongers,

here is my example code trying to update multiple rows with a single =
query. Since the number of lines to be updated varies constantly, both =
the placeholders and the data need to be generated dynamically with each =
call.
=09
Based on the following example =
(https://www.geeksengine.com/database/data-manipulation/update-multiple-ro=
ws-one-query-part1.php) I tried to map it with the following code.

=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94 =
code =
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=20=


#!/usr/bin/perl

use strict;
use warnings;
use feature qw(say);
use DBI();
use Data::Dumper;

use Data::Printer {
  color =3D> {
    'regex'  =3D> 'yellow',
    'hash'   =3D> 'blue',
    'string' =3D> 'cyan',
    'array'  =3D> 'green'
  },
};

my $href =3D {
    FOO =3D>  {
           Company =3D> "Foo Ltd.",
             fw_id =3D> 11111,
           cust_id =3D> 1001,
      vpn_pri_ipv4 =3D> "192.168.1.1",
      vpn_sec_ipv4 =3D> undef
    },
    BAR =3D> {
           Company =3D> "Bar Ltd.",
             fw_id =3D> 22222,
           cust_id =3D> 1234,
      vpn_pri_ipv4 =3D> "172.16.1.1",
      vpn_sec_ipv4 =3D>  undef
    },
    BAZ =3D> {
           Company =3D> "Baz Ltd.",
             fw_id =3D> 33333,
           cust_id =3D> 4321,
      vpn_pri_ipv4 =3D> "10.1.1.1",
      vpn_sec_ipv4 =3D>  undef
    }
};

#delete $href->{BAZ};
#delete @{$href}{'BAR', 'BAZ'};
#delete @{$href}{qw/BAR BAZ/};

db_update_with_fw_obj_ids($href);=20

sub db_update_with_fw_obj_ids {
  =20
   my $data =3D shift;

   my $db_values_to_be_updated;
   foreach (keys %{$data}) {
     $db_values_to_be_updated->{$data->{$_}{cust_id}} =3D =
$data->{$_}{fw_id};
   };
  =20
   my $when_clause =3D join"\n\t", map { "when ? then ?" } (keys =
%{$db_values_to_be_updated});
   my @placeholders =3D ( join',', ('?') x (keys =
%{$db_values_to_be_updated}));

   print "\nWhen-clause for \$dbh->prepare(\$sql): \n", $when_clause, =
"\n";
   print "#"x80, "\n";
   print "\nPlaceholders for \$dbh->prepare(\$sql): ", @placeholders, =
"\n";

   print "#"x80, "\n";
   my $example =3D "
   UPDATE condats SET=20
      fw_obj_id =3D=20
      CASE condats.cust_id
          when '1001' then '111111'
          when '1234' then '222222'
          when '4321' then '333333'
          ELSE fw_obj_id
       END
    WHERE condats.cust_id IN ('1001','1234','4321');
    ";
  =20
   my $dbh =3D =
DBI->connect("DBI:mysql:database=3Ddev;host=3D192.168.200.100","User","Pas=
sword",{'RaiseError' =3D> 1});
   $dbh->do("set character set latin1");
   $dbh->do("set names latin1");

   my $sql =3D <<"EOF_INPUT";
UPDATE condats SET=20
    fw_obj_id =3D=20
    CASE condats.cust_id
        $when_clause
        ELSE fw_obj_id
    END
WHERE condats.cust_id IN (@placeholders);
EOF_INPUT

   print "\n", "#"x30, " SQL query with example data ", "#"x30, "\n";
   p $example;
   print "#"x28, " SQL query with dbi placeholders ", "#"x28, "\n";
   p $sql;

   print "#"x125, "\n";
   my @x =3D map { $_, $db_values_to_be_updated->{$_} } keys =
%{$db_values_to_be_updated};=20
   print "(1st) data set passed as 'map { \$_, =
\$db_values_to_be_updated->{\$_} } keys \%{\$db_values_to_be_updated}' =
to \$sth->execute():\n", Dumper(\@x);=20

   print "#"x125, "\n";
   my @y =3D (keys %{$db_values_to_be_updated});
   print "(2nd) data set passed as 'keys \%{\$db_values_to_be_updated}' =
to \$sth->execute():\n", Dumper(\@y);

   my $sth =3D $dbh->prepare($sql);
   $sth->execute((map { $_, $db_values_to_be_updated->{$_} } (keys =
%{$db_values_to_be_updated})), keys %{$db_values_to_be_updated}) or die =
$DBI::errstr;
   print "Number of rows updated :", $sth->rows, "\n";
   $sth->finish();
   $dbh->disconnect();
}

=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94

What I don't like is the awkward way of dynamically creating the =
placeholders for "$sth =3D $dbh->prepare($sql)" and how the data is =
passed to $sth->execute(). There is one part =E2=80=9Efeeding" the =
values for CASE and another part =E2=80=9Efeeding=E2=80=9C the values =
for "WHERE condats.cust_id IN ('1001','1234','4321=E2=80=99)=E2=80=9C.

Possibly only a "smarter" data structure is needed, from which DBI on =
the one hand takes the data for the CASE part and from another part of =
the data structure data for the WHERE clause?

I wonder if there is a smarter way to create the DBI-placeholder and how =
to hand over the data to $sth->execute() in one shot.

Any recommendations/best practices?

Any advice would be be highly appreciated.

Cheers,

Ritter=