Hello Lukas, Yes it finally worked, I have tested it this time ! I think it is reliable since the solution is 6 years old (http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause/8620162#8620162) and still nothing has changed.
However, I don't know if because of a MySQL limitation, a (big) hack should be added to jOOQ. Maybe we can wait to see if there are other MySQL users interested in this feature before starting to think of an implementation into jOOQ. Alright, that will need to be fixed > Thank you, this will be a great improvement ! Cheers, Aurélien On Wednesday, May 21, 2014 8:27:59 AM UTC+2, Lukas Eder wrote: > > > > > 2014-05-20 22:49 GMT+02:00 Aurélien Manteaux <[email protected]<javascript:> > >: > >> 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.
