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

[Ritter]

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.
        
Based on the following example 
(https://www.geeksengine.com/database/data-manipulation/update-multiple-rows-one-query-part1.php)
 I tried to map it with the following code.

————————————————————————————————— code ————————————————————————————————— 

#!/usr/bin/perl

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

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

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

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

db_update_with_fw_obj_ids($href); 

sub db_update_with_fw_obj_ids {
   
   my $data = shift;

   my $db_values_to_be_updated;
   foreach (keys %{$data}) {
     $db_values_to_be_updated->{$data->{$_}{cust_id}} = $data->{$_}{fw_id};
   };
   
   my $when_clause = join"\n\t", map { "when ? then ?" } (keys 
%{$db_values_to_be_updated});
   my @placeholders = ( 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 = "
   UPDATE condats SET 
      fw_obj_id = 
      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');
    ";
   
   my $dbh = 
DBI->connect("DBI:mysql:database=dev;host=192.168.200.100","User","Password",{'RaiseError'
 => 1});
   $dbh->do("set character set latin1");
   $dbh->do("set names latin1");

   my $sql = <<"EOF_INPUT";
UPDATE condats SET 
    fw_obj_id = 
    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 = map { $_, $db_values_to_be_updated->{$_} } keys 
%{$db_values_to_be_updated}; 
   print "(1st) data set passed as 'map { \$_, \$db_values_to_be_updated->{\$_} 
} keys \%{\$db_values_to_be_updated}' to \$sth->execute():\n", Dumper(\@x); 

   print "#"x125, "\n";
   my @y = (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 = $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();
}

———————————————————————————————————————————————————————————————————————

What I don't like is the awkward way of dynamically creating the placeholders 
for "$sth = $dbh->prepare($sql)" and how the data is passed to $sth->execute(). 
There is one part „feeding" the values for CASE and another part „feeding“ the 
values for "WHERE condats.cust_id IN ('1001','1234','4321’)“.

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