onDuplicateKeyUpdate values

[Chen Wu]

Hi everyone,

I'm wondering what is the correct syntax for generating a query that has SQL 

on duplicate key update `column_name`=VALUES(`column_name`)

? When I try to use onDuplicateKeyUpdate.set(column_name, column_name) it generates 

on duplicate key update `column_name`=`column_name`

which does not update an existing row with new values when inserting into the table.

Thank you

Chen 

[Lukas Eder]

Starting with jOOQ 3.17, we've added support for the PostgreSQL EXCLUDED pseudo table, which works just like MySQL's VALUES() clause and emulates itself that way. Use DSL.excluded() for that purpose:

https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/impl/DSL.html#excluded(org.jooq.Field)

Note that MySQL has deprecated VALUES() and will implement a different syntax in the future, which we'll adopt soon via our EXCLUDED emulation.

I hope this helps,

Lukas

--
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 jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/9171bbfe-2872-4e39-860a-cd34d7632ed7n%40googlegroups.com.

[Chen Wu]

Hi Lukas,

Thank you for your reply. Is there a way to achieve this with version 3.14.9? I'm not able to upgrade jOOQ yet to the latest version.

Thanks,

Chen 

[Lukas Eder]

Then, use plain SQL templating:

https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating/

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/a438f1db-9d4a-43e1-8727-4b4cd755c7b6n%40googlegroups.com.

[Chen Wu]

Got it. Thank you!