使用联接更新jOOQ中的行 [英] Updating rows in jOOQ with joins

问题描述

我相信jOOQ中的更新不支持联接，因此我一直在探索如何解决此问题...

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

我的第一个尝试是使用where in，但是问题是MySQL在FROM子句中不支持目标表:

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();

我的第二次尝试是为USER使用一个临时表(受此的启发).问题是，我不知道如何在select中引用临时表.到目前为止，这是我尝试使用本机SQL:

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'
);

用jOOQ可以实现吗?如果没有，也许我应该对整个查询使用本机SQL代码.

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

我已经设法解决了这个问题，但是它很简陋，所以我仍然对替代方法感兴趣.

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

简单的工作解决方案:

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

推荐答案

我相信jOOQ中的更新不支持联接

I believe joins aren't supported with updates in jOOQ

您可能会认为，因为没有UpdateJoinStep类型，所以很像

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

您可以像其他任何表达式一样将上面的表表达式传递给DSLContext.update(Table).我怀疑这使您剩下的问题过时了吗?

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

这篇关于使用联接更新jOOQ中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！