Thanks for you reply ! jOOQ actually let me write this :
> DSLContext deleteQuery = DSL.using(configuration()); > deleteQuery > .delete( > TABLE_A > .leftOuterJoin(TABLE_B) > .on(TABLE_A.ID.eq(TABLE_B.A_ID)) ) > .where(TABLE_B.A_ID.isNull()) > .execute(); However MySQL does not validate the resulted SQL : > DELETE /* missing alias */ FROM table_a > LEFT JOIN table_b > ON table_a.id = table_b.a_id > WHERE table_b.a_id IS NULL; In my case, the TABLE_A.in() statement will be fast enough since the JOIN subquery will return just a few rows. Anyway thank you for the feature request ! Cheers, Aurélien On Monday, May 19, 2014 7:09:34 AM UTC+2, Lukas Eder wrote: > > Hello, > > That's a valid workaround, although, I suspect that the DELETE FROM .. > JOIN .. statement might be faster, as MySQL is not exactly renowned for its > SQL transformation capabilities, when it comes to optimising SQL. > > Currently, you can express your original query by using the JOIN methods > directly on the table, which should work on DELETE statements in MySQL: > > TABLE_A.leftOuterJoin(TABLE_).on(...) > > > Note that the JOIN methods on the Select statement, as in SelectJoinStep > are mere convenience for the other ones. JOIN is not a SELECT clause, but a > table expression clause. This also allows for nesting joins in more complex > situations. > > Anyway, I have registered a feature request to improve this for those > dialects that support JOIN in DELETE (and UPDATE): > https://github.com/jOOQ/jOOQ/issues/3266 > > Best Regards, > Lukas > > 2014-05-18 21:38 GMT+02:00 Aurélien Manteaux <aman...@gmail.com<javascript:> > >: > >> Actually my query was: >> >>> DELETE a FROM table_a a >>> LEFT JOIN table_b b >>> ON a.id = b.a_id >>> WHERE *b.a_id* IS NULL; >>> >> >> I found a nice workaround : >> >>> DSLContext deleteQuery = DSL.using(configuration()); >>> deleteQuery >>> .delete(TableA) >>> .where(TableA.ID.in( >>> deleteQuery >>> .select(TableA.ID) >>> .from(TableA) >>> .join(TableB, JoinType.LEFT_OUTER_JOIN) >>> .on(TableA.ID.eq(TableB.A_ID)) >>> .where(TableB.A_ID.isNull()) >>> )) >>> .execute(); >>> >> >> I got to admit, jOOQ is powerful :) >> >> However, if it is possible to directly execute the delete statement, I am >> interested ! >> >> Cheers, >> Aurélien >> >> >> On Sunday, May 18, 2014 8:44:47 PM UTC+2, Aurélien Manteaux wrote: >>> >>> Hi, >>> >>> I want to express this statement in jOOQ: >>> >>>> DELETE a FROM table_a a >>>> LEFT JOIN table_b b >>>> ON a.id = b.a_id >>>> WHERE a.id IS NULL; >>>> >>> >>> Unfortunately delete statements seem to work only with the WHERE clause : >>> >>>> DSL.using(configuration()).delete(TableA).join(..) >>>> // Does not exist: -------------------------------> ^^^ >>>> >>> >>> I tried to look in the docs, but the docs server http://jooq.org/learnseems >>> down :( >>> >>> Cheers, >>> Aurélien >>> >> -- >> You received this message because you are subscribed to the Google Groups >> "jOOQ User Group" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to jooq-user+...@googlegroups.com <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
