JOOQ: update two tables with one2one relation

Question

I have 2 tables with 1:(0 or 1) relations for example:

table1 - students

id	last_name	first_name
1	Hart	John

table2 - student_address

id	student_id	address	is_married
1	1	something	true

not every student has an address (for the example)

I want to update both tables, update students and if exist relative row in student_address - update also the student_address. is it possible to do it in one query in JOOQ?

score 0 · Accepted Answer · answered Apr 20 '21 at 08:16

In MySQL, you can update multiple tables in a single statement using JOIN:

create table a (i int primary key, j int);
create table b (i int references a, k int);

insert into a values (1, 1);
insert into b values (1, 1);
update a join b using (i) set j = 2, k = 2;
select * from a join b using (i);

Resulting in

|i  |j  |k  |
|---|---|---|
|1  |2  |2  |

There's nothing special about that from a jOOQ perspective. Just translate it directly to jOOQ:

ctx.update(A.join(B).using(A.I))
   .set(A.J, 2)
   .set(B.K, 2)
   .execute();

JOOQ: update two tables with one2one relation

1 Answers1