title: How to write insert query from slect result which return more then one row using max+1 syntax
body: <p>I am trying to write a SQL like below in DB2.</p>
  
  <pre><code>insert into tableA (col1, col2, col3) 
      select max(col1) + 1, '-2', col3 
      from tableA 
      where col2 = -1
  </code></pre>
  
  <p>Here the aim is copy all the record of tableA from -1 to -2 and here col1 is primary key and this should be increment by 1.</p>
  
  <p>Now the problem is the select query will return more then one row like below</p>
  
  <pre><code>1001 -2 xyz
  1001 -2 pqr
  1001 -2 xdc
  </code></pre>
  
  <p>And it is not able to insert as the key is same.</p>
  
  <p>Is there any way I can write the select sql so that it can return like below</p>
  
  <pre><code>  1001 -2 xyz
    1002 -2 pqr
    1003 -2 xdc
  </code></pre>
  
  <p>and my insert will work, or is there any alternative way to the same with easiest way.</p>
  
  <p>Note: The table do not have any trigger or some thing to auto increment the primary key.</p>