https://kotlinlang.org logo
#komapper
Title
# komapper
d

dave08

11/06/2023, 12:16 PM
Is there any way to have an UPDATE with a JOIN?
t

Toshihiro Nakamura

11/06/2023, 12:27 PM
Although UPDATE with a JOIN is not supported, it might be possible to rewrite by specifying a subquery in the WHERE clause. What kind of SQL do you wish to generate?
d

dave08

11/06/2023, 12:29 PM
I have table
ia
that I need to update,
ia
contains a foreign key to
a
and I need a where on
a.name
for which row in
ia
to update...
In 5.7 there something like this:
Copy code
UPDATE items,
       (SELECT id, retail / wholesale AS markup, quantity FROM items)
       AS discounted
    SET items.retail = items.retail * 0.9
    WHERE discounted.markup >= 1.3
    AND discounted.quantity < 100
    AND items.id = discounted.id;
from the mysql docs
which uses multi table update to just have that table around for the where statement... but in 8, there's a better syntax (which I can't use for now...
so I guess:
Copy code
UPDATE ia,
       (SELECT id, name FROM a)
       AS a1
    SET ia.status = 3
    WHERE ia.user = 20
    AND a1.name = ?;
In the mysql 5.7 docs, this seems invalid for some reason:
Copy code
mysql> UPDATE items
     > SET retail = retail * 0.9
     > WHERE id IN
     >     (SELECT id FROM items
     >         WHERE retail / wholesale >= 1.3 AND quantity > 100);
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause
t

Toshihiro Nakamura

11/06/2023, 12:46 PM
Achieving such SQL with Komapper’s DSL is not possible. Please use an SQL template.
d

dave08

11/06/2023, 1:16 PM
Maybe that example was worse because it used the same table items... I wonder if using inList(subquery) in the where block would work...?
This sql seems to work:
Copy code
update ia
set ia.status = 2
where
    ia.pId in (select p.id from p where p.name = 'someName' and ia.user = 180411;
So I guess it would be:
Copy code
QueryDsl.update(ia).set {
            ia.status eq Status.SomeStatus
        }.where {
            ia.aId inList QueryDsl.from(a).where { a.name eq name }.select(a.id)
        }
t

Toshihiro Nakamura

11/07/2023, 5:28 AM
Yes, that code should work.