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

[Peter J. Holzer]

On 2023-08-28 08:57:51 +0200, Ritter wrote:
> 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 —————————————————————————————————

[...]

> 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}));

[...]

> 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

[...]

> my @x = map { $_, $db_values_to_be_updated->{$_} } keys %{$db_values_to_be_updated};

[...]

> my @y = (keys %{$db_values_to_be_updated});

[...]

> 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;

[...]

> }
>
> ———————————————————————————————————————————————————————————————————————
>
> 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.

I don't think there is a much better way to do it with DBI alone. It is
after all relatively low-level. There may be a higher level "SQL
construction" package out there, maybe something like SQLAlchemy for
Python (which I don't particularly like, but clearly many people do).

> Any recommendations/best practices?

What I would do differently than you is arrange the code so that
construction of the placeholders is right next to construction of the
parameter lists. for example, in your code there are 40 lines between

> my $when_clause = join"\n\t", map { "when ? then ?" } (keys %{$db_values_to_be_updated});

and

> my @x = map { $_, $db_values_to_be_updated->{$_} } keys %{$db_values_to_be_updated};

which makes it hard to ascertain that those two lines actually match (the
non-descriptive name @x doesn't help).

So I would change that to something like

my $when_clause = ...
my @when_params = ...

my $where_clause = ...
my @where_params = ...

and then tie it all together

my $sql = <<"EOF_INPUT";
UPDATE condats SET
fw_obj_id =
CASE condats.cust_id
$when_clause
ELSE fw_obj_id
END

WHERE $where_clause
EOF_INPUT

my $sth = $dbh->prepare($sql);

$sth->execute(@when_params, @where_params) or die $DBI::errstr;

There are a few other nitpicks I have with your code (like, why do you
use map in one line and x in the next for the same purpose? Why is
@placeholders a list with one member, why do you assign to variables and
then compute the same thing again?), but I realise that this is
throw-away test code and those details have nothing to do with your
question.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | h...@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"