Hi, What is the JPQL query that you're issuing that is resulting in that SQL statement? One easy way to get this information is by setting the 'Query' log channel to TRACE.
-Patrick -- Patrick Linskey BEA Systems, Inc. _______________________________________________________________________ Notice: This email message, together with any attachments, may contain information of BEA Systems, Inc., its subsidiaries and affiliated entities, that may be confidential, proprietary, copyrighted and/or legally privileged, and is intended solely for the use of the individual or entity named in this message. If you are not the intended recipient, and have received this message in error, please immediately return this by email and then delete it. > -----Original Message----- > From: Dave [mailto:[EMAIL PROTECTED] > Sent: Monday, March 12, 2007 9:19 AM > To: open-jpa-dev@incubator.apache.org > Subject: How to turn off sub-selects in updates? > > I'm trying to get Apache Roller working with JPA and I've had some > success (thanks to Sun's Craig Russell and Mitesh Meswani) -- I was > able to get 100% of the Roller unit tests working with OpenJPA and > Toplink/JPA and running against Derby and MySQL. > > However, I'd like to be able to use sub-selects and thus far that's > been impossible with OpenJPA. Problem is, if I turn on sub-select > support via SupportsSubselect=true, OpenJPA starts using sub-selects > all over the place -- in places where I didn't code a sub-select -- > and I start getting exceptions like this: > > <0|false|0.9.6-incubating> > org.apache.openjpa.persistence.PersistenceException: You can't specify > target table 'roller_weblogentrytagagg' for upd > ate in FROM clause {prepstmnt 9396085 DELETE FROM > roller_weblogentrytagagg WHERE id IN (SELECT DISTINCT t0.id FROM > roller_weblogentrytagagg t0 WH > ERE (t0.websiteid = ?)) [params=(String) > befd0c00-757c-4aef-b926-008d7d0696cb]} [code=1093, state=HY000] > FailedObject: [EMAIL PROTECTED] > at > org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBD > ictionary.java:3720) > NestedThrowables: > java.sql.SQLException: You can't specify target table > 'roller_tasklock' for update in FROM clause > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975) > at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600) > at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695) > at com.mysql.jdbc.Connection.execSQL(Connection.java:3026) > > > Apparently referencing the update target table in a from-clause > sub-select is not permitted by MySQL. I'm not sure why OpenJPA feels > the need to use sub-selects in updates; it's unnecessary and > problematic. So my question is this: is there some way to keep OpenJPA > from using sub-selects in update queries? > > - Dave >
