Hi, Another opportunity is to use execute_array() instead of execute(). Fore details search for 'execute_array' in 'perldoc DBI'. I use Postgresql in the example below, because I have no MySQL.
sub db_update_with_fw_obj_ids { my $data = shift; my (@cust_id, @fw_id); for (values $data->%*) { push @cust_id, $_->{cust_id}; push @fw_id, $_->{fw_id}; } my $dbh = DBI->connect("dbi:Pg:dbname=test", '', '', {RaiseError => 1}); my $sql = 'update condats set fw_id = ? where cust_id = ?'; my $sth = $dbh->prepare($sql); my $tuples = $sth->execute_array( { ArrayTupleStatus => \my @tuple_status }, \@fw_id, \@cust_id, ); if ($tuples) { print "Successfully updated $tuples records\n"; #print "@cust_id\n"; #print "@fw_id\n"; #print "@tuple_status\n"; } else { for my $tuple (0..@cust_id-1) { my $status = $tuple_status[$tuple]; $status = [0, "Skipped"] unless defined $status; next unless ref $status; printf "Failed to update (%s, %s): %s\n", $cust_id[$tuple], $fw_id[$tuple], $status->[1]; } } $sth->finish(); $dbh->disconnect(); } Regards On Mon, Aug 28, 2023 at 8:58 AM Ritter <li...@netzritter.de> wrote: > 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