Hello, With the jOOQ API, it is generally easier to put the "simple" field on the left hand side of the predicate, like so:
DSL.val(0).eq(selectCount().from(XXX).where(...)) But of course, the other way round works as well. The reason why jOOQ renders "lockedData" instead of the full query is the fact that you aliased it for use in the SELECT clause, where the subquery would be rendered as SELECT (SELECT COUNT(*) FROM XXX WHERE ... ) AS lockedData FROM ... Outside of the SELECT clause, aliased fields are rendered using their field alias only. In your case, the query should be rendered correctly by simply leaving out the field alias when calling asField(). Hope this helps, Lukas 2014-09-24 16:50 GMT+02:00 <[email protected]>: > Hello, > > I am searching a way for doing this : > > UPDATE XXX where *(select count(*) from XXX where ... )=0* and ... > > I have try with correlated subquery but it doesn't work, my subquery > become a field that I use into the update's where clause. But on runtime, > this field is not replace by the suybquery. > > Do you know why ? > > Example : > > // select subquery > Field<Object> lockedData = query > .selectCount() > .from(SUPPORT) > .where(SUPPORT.ID.equal("XXXX") > .and(SUPPORT.BUSINESSLOCK.equal("Process2#1").and( > SUPPORT.BUSINESSLOCK.isNull()))).asField("lockedData"); > > > //update query > int result = query.update(SUPPORT).set(SUPPORT.STATUS, 100) > .where(lockedData.equal(0).and(SUPPORT.ID.equal("XXXXX"))).execute(); > > At runtime, this update is executed : > > update "APP"."SUPPORT" set "APP"."SUPPORT"."STATUS" = 100 where > ("lockedData" = 0 and "APP"."SUPPORT"."ID" = 'AET_SUPPORT') > > Thanks for your help. > > -- > 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.
