Unable to do batch update with Subquery in Insert or Update in Spring boot NamedParameterJdbcTemplate

Question

Am trying to insert mutiple records using NamedParameterJdbcTemplate batchupdate. Am able to insert record without using subquery in Insert statement. But when I use Subquery its returning dataintegrityviolationexception Please find below code for your reference,

 public void insertBatch(){
       String sql = "insert into emp(id, name, age) values ((select max(id) from company where managerid = :managerid), :name, :age)";
       List<Map<String, Object>> batchVales = new ArrayList<Map<String, Object>>();
       batchValues.add(new MapSqlParameterSource().addValue("name", "example").addValue("age", 30).addValue("managerid", 10).getValues());
       SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(batchValues.toArray(new Map[1]));
       jdbcTemplate.batchUpdate(sql, params);
    }

In above code am using Subquery to fetch max(id) from company table. If I use this subquery its returning error. If I remove and hard code some value its working fine. Why batchupdate not accepting sub query. Am using DB2 Database

Answer 1

Only the INSERT INTO ... SELECT ... form is supported; not the INSERT INTO ... VALUES ... form for sub queries.

The pseudo-syntax for INSERT statements is: INSERT INTO EntityName properties_list select_statement.

Try like this:

String sql = "insert into emp(id, name, age) (select max(id) from company where managerid = :managerid), :name, :age";

Updates: As pointed out in comment, it should work like this:

String sql = "insert into emp(id, name, age) select max(id), :name, :age from company where managerid = :managerid;

Extra params which are not part of Select sub query needs to merge in select sub query.