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!"
signature.asc
Description: PGP signature
