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. 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 :( 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 ! Cheers, Aurélien On Tuesday, May 20, 2014 3:08:03 PM UTC+2, Lukas Eder wrote: > > Hi Aurélien, > > 2014-05-19 22:57 GMT+02:00 Aurélien Manteaux <[email protected]<javascript:> > >: > >> 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; >> >> > Curious, that might be a bug. Yes, MySQL and MariaDB have a very peculiar > DELETE syntax, where you should really write something along the lines of > DELETE t1 FROM table_a t1 LEFT JOIN ... > > Have you tried aliasing TABLE_A to something? Would that work? It would > still be a bug, though. I have updated #3266 accordingly > https://github.com/jOOQ/jOOQ/issues/3266 > > >> In my case, the TABLE_A.in() statement will be fast enough since the JOIN >> subquery will return just a few rows. >> > > Alright. > > Cheers > Lukas > -- 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.
