Spring Dao says bad SQL grammar - syntax error

Question

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [DELETE model_state_history FROM model_state_history INNER JOIN model ON model_state_history.model_id = model.id WHERE model.package_id = ?]; nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "DELETE model_state_history FROM[*] model_state_history INNER JOIN model ON model_state_history.model_ID = model.ID WHERE model.PACKAGE_ID = ? "; SQL statement:

And it throws the above in the output. For my eyes, I don't see a syntax error. I am using MySQL database. Is there anything which is obviously wrong in SQL syntax above?

Using the DELETE FROM syntax gives the syntax error too.

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [DELETE FROM model_state_history INNER JOIN model ON model_state_history.model_id = model.id WHERE model.package_id = ?]; nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "DELETE FROM model_STATE_HISTORY INNER[*] JOIN model ON model_STATE_HISTORY.model_ID = model.ID WHERE model.PACKAGE_ID = ? "; SQL statement: DELETE FROM model_state_history INNER JOIN model ON model_state_history.model_id = model.id WHERE model.package_id = ? [42000-176]

And this is the piece of code that is being run

  @Transactional
  private int deleteAllModels(Long packageId) throws DataAccessException {
    return new NamedParameterJdbcTemplate(dataSource).update(
        "DELETE FROM model_state_history INNER JOIN model ON model_state_history.version_id = model.id WHERE model.package_id = :package_id",
        ImmutableMap.of("package_id", packageId));

instead of `DELETE model_state_history FROM model_state_history` it should be `DELETE FROM model_state_history` — Abhik Chakraborty, Nov 11 '14 at 07:40
@SenthilKumaran Try `DELETE model_state_history FROM model_state_history ...`. See this [thread](http://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server) — Jens, Nov 11 '14 at 08:05

Anders R. Bystrup · Answer 1 · 2014-11-11T08:29:15.007

On second thought, I think you might have a problem with the JOIN syntax. You're writing

DELETE FROM ... INNER JOIN ... ON msh.version_id = m.id WHERE m.package_id = :package_id

I'd think that WHERE should be an AND instead, so the query would become:

"DELETE FROM model_state_history INNER JOIN model 
 ON model_state_history.version_id = model.id AND model.package_id = :package_id"

since the WHERE refers back to the DELETE statement, which doesn't have the model table in it's "scope".

Cheers,

Spring Dao says bad SQL grammar - syntax error

1 Answers1