Search code examples
mysqlsqljooq

JOOQ: update two tables with one2one relation

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?

Solution

  • 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();