CODEWITHSUNDEEP
postgresqljooq
oshai
oshai

Reputation: 15355

updating table rows in postgres using subquery with jooq

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?

Upvotes: 1

Views: 522

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

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.

Upvotes: 1

Related Questions