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 <[email protected]>: > 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 [email protected]. > 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
