how to execute a nested query in SPring HSQLDB

Question

I am creating an insert nested script for HSQLDB e.g.

insert into table (col1, col2) values ('val1', (select val2 from table2 where col3='val3'))

When I am trying to execute this query using Spring JDBCTemplate as

jdbcTemplate.execute(query);

it is giving me exeception of BadSQL grammer with unkown token:

However, if I run the same query for MySQL it works fine but for HSQLDB its not1.

Can anybody tell me the issue?

Answer 1

The query syntax is supported by the latest HSQLDB 2.x.

If there is always exactly one row in table2 with col3='val3' then the statement succeeds. But if there is more than one row, it fails. MySQL is known to ignore such details of the SQL Standard.

If this is the problem, then you can use

 insert into table (col1, col2) 
 values ('val1', (select val2 from table2 where col3='val3' limit 1))