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.
