Subquery inside an update to set multiple values-CodePudding

I want to move a DateTime of a record by using update().set(). When I try the given example in the jOOQ docs for using a subselect I found that I needed to cast to a Row2 type because I want to set two variables. With this, the query gets build in an unusual way (or at least not according to what I expect of the query given the example in the jOOQ docs):

public static void moveEventStart(final LocalDate previousEventStart, final LocalDate newEventStart) {
    final Days changeInterval = Days.daysBetween(previousEventStart, newEventStart);
    ActivityTbl a = ACTIVITY.as("a");
    ActivityTbl a2 = ACTIVITY.as("a2")

    jooq()
        .update(a)
        .set(
            (Row2) row(a.START, a.END),
            select(
                 models.Jooq.dateAdd(a2.START, changeInterval.getDays(), DatePart.DAY),
                 models.Jooq.dateAdd(a2.END, changeInterval.getDays(), DatePart.DAY))
            .from(a2)
            .where(...))
        .where(...)
        .execute();
}

The dateAdd function is build like this:

public static Field<Date> dateAdd(TableField<?, LocalDateTime> field, int interval, DatePart datePart) {
    String unit;
    switch (datePart) {
      case YEAR:
        unit = "YEAR";
        break;
      case MONTH:
        unit = "MONTH";
        break;
      case DAY:
        unit = "DAY";
        break;
      case HOUR:
        unit = "HOUR";
        break;
      case MINUTE:
        unit = "MINUTE";
        break;
      case SECOND:
        unit = "SECOND";
        break;
      default:
        unit = "";
    }
    return DSL.field(
        "DATE_ADD({0}, INTERVAL {1} "   unit   ")", SQLDataType.DATE, field, DSL.inline(interval));
  }

Produces:

update `Activity` as `a` 
set(`start`, `end`) = (
    select * from (
        select 
            DATE_ADD(`a2`.`start`, INTERVAL 1 DAY), 
            DATE_ADD(`a2`.`end`, INTERVAL 1 DAY) 
        from `Activity` as `a2` 
        where (...) as `t`
    ) 
where (...)

Why does jOOQ build a select on top of the already given select? And how do I make this work, so it sets the multiple values according to the given select?

CodePudding user response：

The syntax isn't supported by MySQL

MySQL doesn't support this syntax yet. You can see that on the Javadoc of jOOQ's UpdateSetFirstStep.set(Row2, Select) method, which lists these dialects as supported:

@Support({ASE,AURORA_POSTGRES,BIGQUERY,COCKROACHDB,DB2,H2,HANA,HSQLDB,INGRES,ORACLE,POSTGRES,SNOWFLAKE,SQLSERVER,SYBASE})

jOOQ could emulate the syntax:

https://github.com/jOOQ/jOOQ/issues/10522 (Using UPDATE .. FROM for uncorrelated subqueries)
https://github.com/jOOQ/jOOQ/issues/10523 (By splitting the correlated subquery into two)

Both of these emulations aren't available yet as of jOOQ 3.15

If the syntax was supported, why the extra derived table?

If the syntax was supported, then the additional derived table is a workaround implemented by jOOQ for a limitation in MySQL, where an UPDATE or DELETE query cannot reference the DML statement's target table from within a correlated subquery (but magically, this limitations disappears when nesting the correlated subquery one level in a derived table). See: