Hi Maximilian,
Thanks for your message. jOOQ supports the UPDATE .. ROW = ROW syntax for
those databases that also have native support. This is documented in the
API as well, see:
https://www.jooq.org/javadoc/latest/org/jooq/UpdateSetFirstStep.html#set-org.jooq.Row5-org.jooq.Row5-
It contains the following support annotation:
@Support(value={DB2,H2,HANA,HSQLDB,INGRES,ORACLE,POSTGRES})
Unfortunately, MySQL doesn't have native support for row value expression
updates like the other databases (even if row value expressions are
supported in predicates). There's a pending feature request to emulate this
syntax for other databases by transforming the row expressions into
individual single-column assignments:
https://github.com/jOOQ/jOOQ/issues/6884
I hope this helps,
Lukas
2017-12-08 0:22 GMT+01:00 Maximilian Manndorff <[email protected]>:
> Hello,
>
> I ran into an issue with MySQL today. I'm trying to increment several
> columns at once using jooq and have the following statement.
>
> DSL.using(connection, SQLDialect.MYSQL)
>> .update(TABLE)
>> .set(
>> DSL.row(
>> TABLE.COL1,
>> TABLE.COL2,
>> TABLE.COL3,
>> TABLE.COL4,
>> TABLE.COL5),
>> DSL.row(
>> SUBSCRIPTION.COL1.add(1),
>> SUBSCRIPTION.COL2.add(1),
>> DSL.val(0),
>> DSL.val(new Timestamp(System.currentTimeMillis())),
>> DSL.val("message")))
>> .where(TABLE.KEY1.eq(var.getKey1()))
>> .and(TABLE.KEY2.eq(var.getKey2()))
>> .and(TABLE.KEY3.eq(var.getKey3()))
>> .execute();
>>
>>
> I've changed the column and table names for the purposes of this example,
> but everything is the same. This compiles and runs just fine, but I'm
> getting the following runtime exception
>
> *org.jooq.exception.DataAccessException: SQL [update `DB`.`TABLE` set
>> (`COL1`, `COL2`, `COL3`, `COL4`, `COL5`) = ((`DB`.`TABLE`.`COL1` + ?),
>> (`DB`.`TABLE`.`COL2` + ?), ?, ?, ?) where (`DB`.`TABLE`.`KEY1` = ? and
>> `DB`.`TABLE`.`KEY2` = ? and `DB`.`TABLE`.`KEY3` = ?)]; You have an error in
>> your SQL syntax; check the manual that corresponds to your MySQL server
>> version for the right syntax to use near '(`COL1`, `COL2`, `COL3' at line 1*
>>
>
>
> When I try to run a test query myself I can reproduce this behavior. MySQL
> does not seem to like the parentheses around the set columns. I.e. this
> will reproduce the error
>
> *update `DB`.`TABLE` set (`COL1`) = (**`DB`.`TABLE`.`COL1` + 1)*
>
>
> While this works just fine
>
>
> *update `DB`.`TABLE` set `COL1` = **(`DB`.`TABLE`.`COL1` + 1)*
>
>
>
> I'm a little confused why MySQL is complaining, I do not think the
> additional parentheses should matter.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
