Update record on unique constraint on batch store jooq + SQL Error [23505]: ERROR: duplicate key value violates unique constraint

[Sulaiman Malik]

I am trying to insert the records using batchStore using jooq. I need to know how we can update the record on unique constraint, currently it is throwing
an exception that the record already exists

    SQL Error [23505]: ERROR: duplicate key value violates unique constraint

Below is the code

    DSLContext create = getDSLContext();
    List<UserRecord> userRecordList = new ArrayList<>();
    for (Users user : model.getUsers()) {
        User record = create.newRecord(user);
        userRecordList.add(record);
    }
    create.batchStore(userRecordList).execute();

Currently it is inserting the records fine, but when duplicate record found on the basis of unique constraint it should update the record

[Lukas Eder]

Hi Sulaiman,

Thank you very much for your message.

Currently, there is no way for a batch store operation to implement the INSERT .. ON DUPLICATE KEY UPDATE or MERGE semantics that you intend for it to do. Batch store is simply a batch version of the ordinary UpdatableRecord.store() operation, which makes the decision whether to INSERT or UPDATE the record in the client, not the server.

I suggest you actually run a set based INSERT .. ON DUPLICATE KEY UPDATE statement, or a MERGE statement instead.

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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Sulaiman Malik]

Hi Lucas,

Thankyou for your reply,

I have done it with the different approach, by using a common interface UpdatableRecord, first I have used a fetchOne() query to get the record on the basis of the unique constraint which will give the UpdatableRecord and then set the values with the updated ones which are then added to userRecordlist which will be further passed into batchStore which will decide either to insert or update. I think this approach is also fine. What you say?

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Hi Sulaiman,

Code explains code much better than words do :)

I'm not sure what exactly you did there, given your description. Sounds good, I guess?

Cheers,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.