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