Is there any way to have an UPDATE with a JOIN kotlinlang #komapper

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?

dave08

11/06/2023, 12:29 PM

I have table

ia

that I need to update,

ia

contains a foreign key to

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

Toshihiro Nakamura

11/06/2023, 12:46 PM

Achieving such SQL with Komapper’s DSL is not possible. Please use an SQL template.

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

Toshihiro Nakamura

11/07/2023, 5:28 AM

Yes, that code should work.