Update child table with different where condition in Jooq

I have two tables (A & B) A.Id is a foreign key in table B (and a record in table B may or may not exist). I want to update Table A based on some condition and irrespective of record exists in Table B. Also want to update table B if it contains a record of A.Id. How do we do this multiple table update on different condtions in a single execute in Jooq?

Solution

In most database products, you can't update multiple tables in a single statement, so you'll have to run separate statements in jOOQ. By "single execute", you probably mean you want to have the experience of a single operation in the jOOQ API (irrespective of how many statements are being generated), but that also doesn't exist.

So, you just run multiple statements:

ctx.update(A)
   .set(A.X, someValue)
   .where(someCondition)
   .execute();

ctx.update(B)
   .set(B.Y, someValue)
   .where(someCondition)
   .execute();

If, for some reason, the single round trip to the server is important to you, you can use the procedural language API in jOOQ to wrap the above in an anonymous block:

ctx.begin(
    update(A)
    .set(A.X, someValue)
    .where(someCondition),

    update(B)
    .set(B.Y, someValue)
    .where(someCondition)
).execute();

Or, you can batch the statements, but they will be separate statements.