CODEWITHSUNDEEP
postgresqlsql-deletejooq
noname
noname

Reputation: 21

JOOQ delete with join

How can I write a JOOQ delete query to join on a field from a with clause? Is it possible?

I have an SQL query that is working but I don't know how to convert it to JOOQ.

This is my query:

with "temp" as (
    select field1 as field1
    from "table"
             join ("table2")
                  on (table.field1 = table2.field2)
)
delete from table using temp
where table.field1 = temp.field1;

I tried:

transactionDSLContext.with("temp")
dsl.as(
                                    select(TABLE.FIELD1.as("field1"))
                                            .from(TABLE)
                                            .join(TABLE2)
                                            .on(TABLE.FIELD1.eq(TABLE2.FIELD2))

                            )
                            .delete(TABLE)
                            .where(TABLE.FIELD1.eq((Field<String>) temp.field("field1"))
                            .execute();

But I'm getting:

ERROR: missing FROM-clause entry for table "temp"

Upvotes: 2

Views: 857

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

You forgot the using clause:

.delete(TABLE).using(temp)

I'm assuming that you have a local variable temp where you assigned your CommonTableExpression...

Upvotes: 0

Related Questions