Updating rows in jOOQ with joins

Question

I believe joins aren't supported with updates in jOOQ, so I've been exploring how to work around it...

My first attempt was to use where in, but the problem is that MySQL doesn't support target tables in the FROM clause:

create
    .update(USER)
    .set(USER.name, concat(USER.NAME, "some text"))
    .where(USER.ID.in(
        create
            .select(USER.ID)
            .from(USER)
            .join(TEAM)
            .on(USER.TEAM_ID.eq(TEAM.ID))
            .where(TEAM.STATE.equal("test"))
    ))
    .execute();

My second attempt was to use a temporary table for USER (inspired by this answer). The issue is, I can't figure out how to reference a temporary table in the select. Here is my attempt so far using native SQL:

create
    .update(USER)
    .set(USER.name, concat(USER.NAME, "some text"))
    .where(USER.ID.in(
        create
            .select("user_nested.id") // This line doesn't work
            .from("SELECT * FROM user AS user_nested")
            .join(TEAM)
            .on("user_nested.team_id = team.id")
            .where(TEAM.STATE.equal("test"))
    ))
    .execute();

The query I ultimately want to end up with is something like:

UPDATE user
SET user.name = concat(user.email, 'some text')
WHERE user.id IN (
    SELECT user_nested.id
    FROM (SELECT * FROM user) AS user_nested
    JOIN team
    ON user_nested.team_id = team.id
    WHERE team.state = 'test'
);

Is this possible to achieve with jOOQ? If not, perhaps I should use the native SQL code for the whole query.

Edit: I have managed to get this working but it's pretty janky, so I'm still interested in alternative approaches.

Janky working solution:

Field<Long> userId = DSL.field("user_nested.id", Long.class);
create
    .update(USER)
    .set(USER.NAME, (concat(USER.NAME, "some text")))
    .where(USER.ID.in(
        create
            .select(userId)
            .from("(SELECT * FROM user) AS user_nested")
            .join(TEAM)
            .on("user_nested.team_id = team.id")
            .where(TEAM.STATE.equal("test"))
    ))

score 7 · Answer 1 · edited Jun 11 '20 at 07:51

I believe joins aren't supported with updates in jOOQ

You're probably thinking that because there's no UpdateJoinStep type much like there's a SelectJoinStep that using a join with updates is not possible in jOOQ. But beware that SelectJoinStep is mere convenience. The JOIN operator is an operator that connects two tables, not a keyword in SQL. Thus, jOOQ supports it as an operator on the Table type. Using your SQL update as an example:

Field<Long> userId = DSL.field("user_nested.id", Long.class);
create
    .update(USER.join(TEAM).on(TEAM.ID.eq(USER.TEAM_ID)))
    .set(USER.NAME, (concat(USER.NAME, "some text")))
    .where(TEAM.STATE.equal("test"))

You can pass the above table expression to DSLContext.update(Table) like any other. I suspect this obsoletes your remaining question?

Updating rows in jOOQ with joins

1 Answers1

Linked