Hi Lukas, Thanks so much for the explanation! This is likely not JOOQ related but...
What I'm doing now is: val p = TestOnDuplicateKeyUpdateRow() p.a = "a0" p.b = "b2" val r = TaskContext.get().jooq .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE) .set(p) .onDuplicateKeyUpdate() .set(p) .returning(Tables.TEST_ON_DUPLICATE_KEY_UPDATE.ID) .fetch() What's happening is: - If the key didn't exist, one row with a new ID is returned correctly - If the key existed, two rows are returned... why? Looks like the first one has the key of the updated row... Can I actually rely on this? I wish there was a simple upsert method on the Record :-) Thanks! On Monday, April 16, 2018 at 12:35:25 AM UTC-7, Lukas Eder wrote: > > 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 <[email protected] <javascript:>>: > >> 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 [email protected] <javascript:>. >> 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.
