Is there any way to have an UPDATE with a JOIN?

# komapper

[dave08]

Is there any way to have an UPDATE with a JOIN?

[Toshihiro Nakamura]

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]

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

[dave08]

In 5.7 there something like this:

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;

[dave08]

from the mysql docs

[dave08]

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

[dave08]

so I guess:

UPDATE ia,
       (SELECT id, name FROM a)
       AS a1
    SET ia.status = 3
    WHERE ia.user = 20
    AND a1.name = ?;

[dave08]

In the mysql 5.7 docs, this seems invalid for some reason:

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]

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

[dave08]

Maybe that example was worse because it used the same table items... I wonder if using inList(subquery) in the where block would work...?

[dave08]

This sql seems to work:

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:

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]

Yes, that code should work.