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

Naresh J · Accepted Answer · 2021-03-11T07:17:53.017

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.

edited Mar 11 '21 at 07:17

answered Mar 03 '21 at 11:09

Naresh J

2,087
5
26
39

I tried it. return PrepareStatement call ; bad Sql Grammar exception – Jai Mar 03 '21 at 11:45
Have you tried by removing brackets of sub query too? or try by just removing `values` from your original query. – Naresh J Mar 03 '21 at 12:53
I tried both. Facing same issue. Am using DB2 database – Jai Mar 03 '21 at 13:33
I tried using Insert into...select. But unable to set multiple values with comma separate. Like Insert into table (id,name,age) select id from company, :name, :age. In this query am unable set value (:name, :age) after select statement. – Jai Mar 07 '21 at 14:11
I accept this Answer and small modification in your answer : we can't use Insert into...select with multiple named parameter. We can use like below, String sql = "insert into emp(id, name, age) select max(id), :name, :age from company where managerid = :managerid;. I referred this link : https://stackoverflow.com/questions/9692319/how-can-i-insert-values-into-a-table-using-a-subquery-with-more-than-one-result – Jai Mar 08 '21 at 15:21
1

Updated your suggestion in answer. – Naresh J Mar 11 '21 at 07:15

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

1 Answers1