updating table rows in postgres using subquery with jooq

Question

I would like to execute and update similar to this:

UPDATE dummy
SET customer=subquery.customer,
    address=subquery.address,
    partn=subquery.partn
FROM (SELECT address_id, customer, address, partn
      FROM  dummy) AS subquery
WHERE dummy.address_id=subquery.address_id;

Taken from this answer: https://stackoverflow.com/a/6258586/411965

I found this and wondered if this can auto converted to jooq fluent syntax.

What is the equivalent jooq query? specifically, how do I perform the outer when referencing the subquery?

Answer 1

Assuming you're using code generation, do it like this:

Table<?> subquery = table(
  select(
    DUMMY.ADDRESS_ID,
    DUMMY.CUSTOMER,
    DUMMY.ADDRESS,
    DUMMY.PARTN
  )
  .from(DUMMY)
).as("subquery");

ctx.update(DUMMY)
   .set(DUMMY.CUSTOMER, subquery.field(DUMMY.CUSTOMER))
   .set(DUMMY.ADDRESS, subquery.field(DUMMY.ADDRESS))
   .set(DUMMY.PARTN, subquery.field(DUMMY.PARTN))
   .from(subquery)
   .where(DUMMY.ADDRESS_ID.eq(subquery.field(DUMMY.ADDRESS_ID)))
   .execute();

Of course, the query makes no sense it is, because you're just going to touch every row without modifying it, but since you copied the SQL from another answer, I'm assuming your subquery's dummy table is really something else.