34

I'm trying to perform a bulk delete of an object, Feature, which has a birdirectional ManyToOne relationship with another class, FeaturesMetadata. I'm having a SQLGrammerException thrown.

The hql I'm using:

String hql = "delete from Feature F where F.featuresMetadata.stateGeoId = :stateGeoId";

Turning on show SQL, the following is generated:

 delete from FEATURE cross join FEATURESMETADATA featuresme1_ where STATEGEOID=?

Running the SQL directly in the db client gives this exception:

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cross join FEATURESMETADATA featuresme1_ where stategeoid='01'' at line 1

Since the generated SQL is throwing the Exception, I tried changing dialects from MySQL5InnoDBDialect to MySQLInnoDBDialect, but no change.

Can anyone assist?

Improve this question
2
  • Does FeaturesMetaData have a FK to Feature? Commented Aug 30, 2011 at 16:33
  • Feature has a FK to FeaturesMetadata. Commented Aug 30, 2011 at 16:37

3 Answers 3

Reset to default
50

You may not have joins in such a HQL query. Quote from the reference documentation:

No joins, either implicit or explicit, can be specified in a bulk HQL query. Sub-queries can be used in the where-clause, where the subqueries themselves may contain joins.

So I guess something like this should work:

delete from Feature F where F.id in 
    (select f2.id from Feature f2 where f2.featuresMetadata.stateGeoId = :stateGeoId)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Doh! I've read that several times and still didn't click. Answer accepted.
Hi, I'm surprised it worked for you since I'm getting: org.hibernate.exception.GenericJDBCException: You can't specify target table 'Users' for update in FROM clause. It happens since you can't modify the same table which you use in the SELECT part (in your case - the Feature table). This behaviour is documented at: dev.mysql.com/doc/refman/5.6/en/update.html. Any suggestions?
Wow. I thought HQL was about making queries more simplified and object-oriented than SQL. I don't see why Hibernate can't figure out on its own how to translate the relationship between the objects into an SQL query without an explicit join.
Can't understand how this answers to the main question: in the HQL he's not making any join...
@Phate He's doing an implicit join, you can see that Hibernate translates the query to a cross join.
1

I had the same issue and struggled to find a sensible answer. It seems that, even if you get this approach to work, the SQL generated is highly inefficient (according to what I have read).

So I took a step back and did the following:

    List<Properties> props = propDao.findPropertiesByHotelCode(hotel.getCode());
    propDao.deleteInBatch(props);
    propDao.flush();

Basically rather tan trying to 'delete where', I'm doing a select where and then deleting in batch the set that I retrieved.

Hope this helps.

Improve this answer

Comments

0

This is indeed rather poor from Hibernate. But you can solve it like this in a repo: (at least in PostgreSQL, not sure if this syntax should be modified for MySql)

@Modifying
@Query(nativeQuery = true, value = """
    DELETE FROM feature f
    USING features_metadata fd
    WHERE f.features_metadata_id = fd.id AND fd.state_geo_id = :stateGeoId
    """)
void deleteByStateGeoIdId(@Param("stateGeoId") UUID stateGeoId);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.