2014-05-20 22:49 GMT+02:00 Aurélien Manteaux <[email protected]>: > Hi Lukas, > > If I try to alias TABLE_A, I get : > >> DELETE /* missing alias */ FROM table_a *as a* >> >> LEFT JOIN table_b >> ON table_a.id = table_b.a_id >> WHERE table_b.a_id IS NULL; >> > But the problem is the same. >
Alright, that will need to be fixed > However, I just realized the TABLE_A.in() statement does not work because > MySQL does not allow to *delete from a table and select from the same > table in a subquery*: > http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause:( > Yes, that is a very annoying limitation. > > So to finally overcome this last issue I have to do the following: > >> DSLContext ctx = DSL.using(configuration); >> >> Table<Record1<Long>> tmpTable = ctx >> .select(TABLE_A.ID) >> .from( >> TABLE_A >> .leftOuterJoin(TABLE_B) >> .on(TABLE_A.ID.eq(TABLE_B.A_ID)) >> ) >> .where(TABLE_B.A_ID.isNull()).asTable("tmp"); >> >> ctx >> .delete(TABLE_A) >> .where(TABLE_A.ID.in(ctx.select((Field<Long>)tmpTable.field("id")) >> .from(tmpTable))) >> .execute(); >> > > to get the "correct" SQL code compatible MySQL: > >> delete from `table_a` >> where `table_a`.`id` in ( >> select `tmp`.`id` >> from ( >> select `table_a`.`id` >> from `table_a` >> left outer join `table_b` >> on `table_a`.`id` = `table_b`.`a_id` >> where `table_b`.`a_id` is null >> ) as `tmp` >> ) >> > > MySQL can be tricky sometimes ! > Wow, and that worked?? Does that work reliably? If it does, it might be worth implementing some abstraction in jOOQ, which handles this sort of SQL transformation for you, transparently. I.e. whenever the TABLE_A (from which we delete) is also referenced in the predicates, we'll transform the predicates in an equivalent way. Might not be too easy, though. -- 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.
