Hello,

Thanks for your message. For the record (as this feature has been requested
a few times), there is no UpdatableRecord.insertOnDuplicateKeyUpdate()
method because the "on duplicate key" semantics is different depending on
the database. In MySQL, all unique keys and indexes are considered for
duplicate keys. In other databases, only the primary key is considered, and
in other databases, the key has to be specified explicitly. It would be
difficult to make the right API choice here.

Regarding your attempted workaround: it would be unexpected for a statement
to modify the passed record just because it happens to be a record and
because a call on the record itself does update the record. You could even
pass different records to the insert / update clauses, or several records
to the insert clause, when the update clause is always a bulk update.

Think of passing a record to a jOOQ statement in the same way as of passing
a map or individual values to a jOOQ statement. So, your statement is akin
to:

jooq
 .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
 .set(p.intoMap())
 .onDuplicateKeyUpdate()
 .set(p.intoMap())
 .execute()


You can use the returning clause on the INSERT statement, to be explicit
about this:

jooq
 .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
 .set(p.intoMap())
 .onDuplicateKeyUpdate()
 .set(p.intoMap())
 .returning()

 .fetch()


I hope this helps,
Lukas

2018-04-15 4:39 GMT+02:00 <alamo...@gmail.com>:

> Hello,
> Thanks for the great product!
>
> I am trying to do "upsert" (insert on duplicate key update) while still
> returning ID for the row.
>
> This is my table:
>
> create table test_on_duplicate_key_update (
>   id bigint not null primary key auto_increment,
>
>   a varchar(255) not null,
>   unique(a),
>
>   b varchar(255)
> );
>
> This is how I perform regular insert (Kotlin):
>
> val p = jooq.newRecord(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
> p.a = "a0"
> p.b = "b0"
> p.store()
> // p.id is available here
>
>
> Now for "upsert", what I do is:
>
> val p = TestOnDuplicateKeyUpdateRow()
> p.a = "a1"
> p.b = "b1"
> jooq
>  .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
>  .set(p)
>  .onDuplicateKeyUpdate()
>  .set(p)
>  .execute()
> // p.id is null :-(
>
>
> What is the idiomatic way to do this?
>
>
> I am using:
>
> - Jooq 3.10.6
>
> - MySQL
>
>
> Thanks!
>
> --
> 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+unsubscr...@googlegroups.com.
> 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 jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to