如何将指定MariaDB更新SQL转换为jOOQ DSL？

原MariaDB更新语句 #

update survey_definition sd
    join (select si.survey_def_id, si.status, si.end_date
          from survey_instance si
          where si.survey_def_id = ?1
            and si.status not in (14)
          order by si.end_date desc
          limit 1) latest on sd.id = latest.survey_def_id
set sd.latest_result=latest.status,
    sd.latest_survey_end_date=latest.end_date;

需求与问题 #

需要将上述SQL转换为jOOQ DSL（使用jOOQ 3.12.1版本），尝试过程中遇到方法解析错误。

错误的尝试代码 #

dslContext.update(SURVEY_DEFINITION
    .join(dslContext.select (SURVEY_INSTANCE.SURVEY_DEFINITION_ID, SURVEY_INSTANCE.STATUS, SURVEY_INSTANCE.END_TIME_MILLIS)
        .from(SURVEY_INSTANCE)
        .where(SURVEY_INSTANCE.SURVEY_DEFINITION_ID.eq(definitionId))
        .limit(1)
        .on (SURVEY_INSTANCE.ID.eq(SURVEY_DEFINITION.ID))
    )

上述代码中，.on()方法触发错误：Cannot resolve method 'on' in 'SelectLimitPercentStep'

最终解决方案 #

final Table<Record3<String, ScanStatus, Long>> latest = dslContext
    .select(SURVEY_INSTANCE.SURVEY_DEFINITION_ID, SURVEY_INSTANCE.STATUS, SURVEY_INSTANCE.END_TIME_MILLIS)
    .from(SURVEY_INSTANCE)
    .where(DSL.and(
        SURVEY_INSTANCE.SURVEY_DEFINITION_ID.eq(definitionId),
        SURVEY_INSTANCE.STATUS.notEqual(ScanStatus.DELETED))
    )
    .orderBy(SURVEY_INSTANCE.END_TIME_MILLIS.desc())
    .limit(1)
    .asTable("latest");
dslContext.update(SURVEY_DEFINITION
        .join(latest)
        .on(latest.field(SURVEY_INSTANCE.SURVEY_DEFINITION_ID).eq(SURVEY_DEFINITION.ID)))
    .set(SURVEY_DEFINITION.LATEST_SCAN_STATUS, latest.field(SURVEY_INSTANCE.STATUS))
    .where(SURVEY_DEFINITION.ID.eq(definitionId))
    .execute();

内容的提问来源于stack exchange，提问作者Sebastian Lore