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

--yewg3f5kdv6a4zol
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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.
> =09
> Based on the following example
> (https://www.geeksengine.com/database/data-manipulation/update-multiple-r=
ows-one-query-part1.php)
> I tried to map it with the following code.
>=20
> =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
 code =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=20
[...]
>    my $when_clause =3D join"\n\t", map { "when ? then ?" } (keys %{$db_va=
lues_to_be_updated});
>    my @placeholders =3D ( join',', ('?') x (keys %{$db_values_to_be_updat=
ed}));
[...]
>    my $sql =3D <<"EOF_INPUT";
> UPDATE condats SET=20
>     fw_obj_id =3D=20
>     CASE condats.cust_id
>         $when_clause
>         ELSE fw_obj_id
>     END
> WHERE condats.cust_id IN (@placeholders);
> EOF_INPUT
[...]
>    my @x =3D map { $_, $db_values_to_be_updated->{$_} } keys %{$db_values=
_to_be_updated};=20
[...]
>    my @y =3D (keys %{$db_values_to_be_updated});
[...]
>    my $sth =3D $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::err=
str;
[...]
> }
>=20
> =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94
>=20
> What I don't like is the awkward way of dynamically creating the
> placeholders for "$sth =3D $dbh->prepare($sql)" and how the data is
> passed to $sth->execute(). There is one part =E2=80=9Efeeding" the values=
 for
> CASE and another part =E2=80=9Efeeding=E2=80=9C the values for "WHERE con=
dats.cust_id
> IN ('1001','1234','4321=E2=80=99)=E2=80=9C.
>=20
> 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?
>=20
> 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=20

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

and

>    my @x =3D map { $_, $db_values_to_be_updated->{$_} } keys %{$db_values=
_to_be_updated};=20

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 =3D ...
    my @when_params =3D ...

    my $where_clause =3D ...
    my @where_params =3D ...

and then tie it all together

    my $sql =3D <<"EOF_INPUT";
UPDATE condats SET=20
    fw_obj_id =3D=20
    CASE condats.cust_id
        $when_clause
        ELSE fw_obj_id
    END
WHERE $where_clause
EOF_INPUT

    my $sth =3D $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

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email protected]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--yewg3f5kdv6a4zol
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmTsa2sACgkQ8g5IURL+
KF2zMRAAkzAIeel4BYZa6fzTSCKAQ4YfujfW+1FQKkVi9KkCYLhDE5AtbX8UB4nF
++fQhOdO7e7W0caMYOMqdPjftCYyRuOa8Q86eimU9r57GnCjnDao3S3Pmf9RaCXD
7aybyXozqK5/2wZyJRiROhOPtvQvA1clx+y/VvzwB+QjdrJTARHwGAgrXxRlR6ny
gTDCE2j7vMFBUiQXe6TdBCjIoR//aNlkZdevXIL+6naPuErF9I8lhRLKOkhTaWyO
v6IW8BKmUp7U9XnTRqB+ZN7aV03fnMdgQ9IFtXtH7c8SYB2LGJlQUwgR50swiAUJ
xITj2+ZfIqHV1uClM2INciqFuHZEcKZFY2GqPji0lzCPVBwWCurYw4V8G6PUVSD3
3olVdF8Z4M3kMdx04t1fMX3NbJtSZVNeoCAFfRDezqFMz3/lWSd68eUywlYMADIa
JBSjhZeoGhnCAPz3sEH2cDS1Hvd17eHmybIuypsKajqjmzAHkfbWG4Yob/+e2O/f
zPuBDCaJPpw0njEiL1qjJHxtXiwjJosvGXddBoMyVBk4F6rJYlyYoi6nuMY9by+z
QrHd2YPArc1VAvOoMC7dFLYJxl8eRYvV/ZT7bASyRGA5y31SrfhbErIHL5ZzNV57
3oYFLIYOr1orUCtfD+IsWOT0PAllnlFrz/jNPRRk2+U57ltpfE4=
=ht4m
-----END PGP SIGNATURE-----

--yewg3f5kdv6a4zol--