How to only update some fields, when using jOOQ's batch loader with onDuplicateKeyUpdate?

Question

Is it possible to only update some fields when a duplicate is found, using jOOQ's batchAll() loader? Similar to how onDublicateKeyUpdate() can be used for single records: https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/insert-statement/insert-on-duplicate-key/.

The code I want to change, to only update one field when there's a duplicate:

dsl.loadInto(TABLE)
        .batchAll()
        .onDuplicateKeyUpdate()
        .loadRecords(records)
        .fields(TABLE.fields())
        .execute();

score 1 · Answer 1 · answered Apr 22 '21 at 08:06

No, that's not possible with the Loader API, but you can use ordinary batching of individual statements either programmatically using the batch API or maybe even using a batched connection, which collects all of your JDBC statements and delays there execution until a batch can be executed:

dsl.batched(c -> {
    for (Record record : records) {
        c.dsl().insertInto(TABLE)
               .set(record)
               .onDuplicateKeyUpdate()
               .set(TABLE.FIELD_OF_INTEREST, record.get(TABLE.FIELD_OF_INTEREST))
               .execute(); // Actual execution is delayed until the end of the lambda
    }
}); // Now, all collected statements are batched together

How to only update some fields, when using jOOQ's batch loader with onDuplicateKeyUpdate?

1 Answers1