JPA error in your SQL syntax

Question

I'm using Spring Data and created the following query:

public interface UserRepository extends JpaRepository<User, Long>, QueryDslPredicateExecutor<User> {

    @Modifying
    @Query("DELETE FROM User u WHERE u.userDetails.userName = :username")
    public void deleteByUserName(@Param("username") String userName);
}

In runtime I keep on getting:

ERROR: org.hibernate.engine.jdbc.spi.SqlExceptionHelper - 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 UserDetails userdetail1_ where User_Name='rb2x5yv7'' at line 1

I'm using MySql with MySQL5Dialect.

What is wrong with my query?

**

UPDATE:

**

Following this thread I have changed my code:

public interface UserRepository extends JpaRepository<User, Long>, QueryDslPredicateExecutor<User> {

    @Modifying
    @Query("DELETE FROM User u WHERE u.id in (SELECT u1.id FROM User WHERE u1.userDetails.userName = :username)")
    public void deleteByUserName(@Param("username") String userName);
}

But now I'm getting:

org.hibernate.QueryException: Unable to resolve path [u1.id], unexpected token [u1] [DELETE FROM com.bs.dal.domain.User u WHERE u.id in (SELECT u1.id FROM com.bs.dal.domain.User WHERE u1.userDetails.userName = :username)]

**

UPDATE 2:

**

It appears I was missing "u1" in the sub-query. Changed the query to:

 @Query("DELETE FROM User u WHERE u.id in (SELECT u1.id FROM User u1 WHERE u1.userDetails.userName = :username)")

Now I'm getting:

org.hibernate.exception.GenericJDBCException: You can't specify target table 'Users' for update in FROM clause.

I know it happens since you can't modify the same table which you use in the SELECT part.This behaviour is documented at: dev.mysql.com/doc/refman/5.6/en/update.html.

How can I work it around? Any suggestions?

Duplicate of http://stackoverflow.com/questions/7246563/hibernate-exception-on-mysql-cross-join-query — Aleksander Blomskøld, Dec 19 '12 at 20:47
Thank you. Following your comment I have changed my code but still can't seem to make it work. Updating the question. — forhas, Dec 20 '12 at 08:52

score 1 · Accepted Answer · edited May 23 '17 at 12:30

To make it work I had to do 2 things:

The first, using this query:

public interface UserRepository extends JpaRepository<User, Long>, QueryDslPredicateExecutor<User> {

    @Modifying
    @Query(name = "Delete User by UserName", value = "DELETE FROM User u WHERE u.id IN (SELECT ud.id FROM UserDetails ud WHERE ud.userName = :username)")
    public void deleteByUserName(@Param("username") String userName);
}

The second:

I have to make sure the delete-cascade is well implemented in the DB. For some reason, it was not generated automatically. You can see my new thread that relates to this point.

Álvaro González · Answer 2 · 2012-12-23T09:46:10.873

0

The proper way to assign a table alias in a DELETE statement would be this:

DELETE u FROM User u WHERE u.userName = :username
       ^

Of course, if there's only one table involved, you can simply omit aliases:

DELETE FROM User WHERE userName = :username

edited Dec 23 '12 at 09:46

answered Dec 21 '12 at 09:30

Álvaro González

142,137
41
261
360

Thanks Alvaro. Your suggestion is actually similar to my first attempt, but no joins, either implicit or explicit, can be specified in a bulk HQL query, as described here: http://stackoverflow.com/questions/7246563/hibernate-exception-on-mysql-cross-join-query – forhas Dec 22 '12 at 10:54

JPA error in your SQL syntax

UPDATE:

UPDATE 2:

2 Answers2