I haven't seen that syntax used before with MySQL. Have you tried the other
overloading?
.set(TABLE.COL1, SUBSCRIPTION.COL1.add(1))
.set(TABLE.COL2, SUBSCRIPTION.COL2.add(1))
...
I personally find this syntax a little easier to read. See if this works
for you.
On Thursday, December 7, 2017 at 6:22:59 PM UTC-5, Maximilian Manndorff
wrote:
>
> 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.
