Search code examples
mariadbjooq

Jooq update table records without primary key unexpectedly inserts new records

I have a table called result that has no primary key (cannot change that). I want to update all the records that meet a certain condition using Jooq. The following code doesn't work as I expected. It inserts new records instead of updating existing ones, which I don't understand.

public Mono<Result> update(Result result, String name) {
        return Mono.from(ctx.update(RESULT)
                        .set(RESULT.STATUS, result.getStatus())
                        .where(RESULT.NAME.eq(name))
                        .returning()
                )
                .map(r -> r.into(Result.class));
Solution

  • jOOQ 3.18 introduced an emulation for UPDATE .. RETURNING in MariaDB 10.5, which doesn't support this syntax natively:

    In short, this kind of statement:

    UPDATE t
SET a = b
WHERE p
ORDER BY o
LIMIT l
RETURNING x;

    Can be emulated as follows:

    INSERT INTO t
SELECT * FROM t WHERE p ORDER BY o LIMIT l
ON DUPLICATE KEY UPDATE SET a = b
RETURNING x;

    But obviously, this assumes the presence of a primary key (or unique key) which can be matched by the ON DUPLICATE KEY clause. In other words, jOOQ can emulate UPDATE .. RETURNING only if you have some unique constraint, not otherwise.

    It is debatable whether this requirement should be indicated to the user in form of an error: