It seems very hard to do this simple thing:
Delete a bunch of rows based on a select that involves a join with another table.
What I would expect to work
Code:
delete from Offer where offer.vendor = :vendor and entryDate < :olderThan and offer.validity.complete = false
This turns out to be illegal (using joins in a delete statement in Hibernate).
What works in some databases but not in MySQL. MySQL doesn't allow you to reference the same table in both a delete and a select statement (Ridiculous, I know.)
Code:
delete from Offer where id in (select offer.id from Offer offer where offer.vendor = :vendor and entryDate < :olderThan and offer.validity.complete = false)
What is the workaround that MySQL is proposing (translated from sql to hql)
Code:
delete from Offer where id in (select id from (select offer.id from Offer offer where offer.vendor = :vendor and entryDate < :olderThan and offer.validity.complete = false) as TMP)
Not surprisingly, this doesn't work in Hibernate. Is there any way to make this work as a simple statement in Hibernate? Or do I have to separate into two queries? Simple things should be simple.
Cheers,
Marc
Login
FAQ
Search
