Thank you both and sorry for the delayed response. I've verified that the syntax Bill suggested works with MySQL. I must have missed the section in the linked documentation. While adding this syntax for MySQL would be nice, I do agree that it is probably low priority compared to other feature requests.
________________________________ From: [email protected] <[email protected]> on behalf of Lukas Eder <[email protected]> Sent: Monday, December 11, 2017 12:12:22 AM To: [email protected] Subject: Re: MySQL Dialect Error on Increment 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]<mailto:[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]<mailto:[email protected]>. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/dZaZBaFHqbI/unsubscribe. To unsubscribe from this group and all its topics, send an email to [email protected]<mailto:[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.
