title: Mysql: Insert values to gaps in sequence
body: <p>So my question is this: What is the best way to check what rows are missing from 1 - 14 and then to <code>INSERT</code> my new value into the open row.</p>
  
  <p>I know that it is not necessary to fill the gaps in your database, but I am doing a course in php and I need to achieve the following:</p>
  
  <p>I have a table with <code>primary key</code> set to <code>id</code>, but not <code>auto_increment</code>. So if one of the 14 values get deleted the new value needs to be placed in the gaps.</p>
  
  <p>I have read up a little bit and found a few question on SO, but they all just want to check if the gaps exist, I somehow need to tell it what and where it is allowed to update the values.</p>
  
  <p>I have this</p>
  
  <pre><code>SELECT MIN(t1.position_id)+1 AS unused
      FROM playerposition AS t1
      WHERE NOT EXISTS (SELECT * FROM playerposition AS t2 WHERE t2.position_id = t1.position_id+1)
      UNION
      -- Special case for missing the first row
      SELECT 1
      FROM DUAL
      WHERE NOT EXISTS (SELECT * FROM playerposition WHERE position_id = 1)
  ) AS subquery
  </code></pre>
  
  <p>But that only returns the first row and I don't really understand the query. Any help, advice or guidance would be appreciated. </p>
  
  <p>UPDATE</p>
  
  <p>I have made a change to the query and I now get the empty values. My new query looks like this</p>
  
  <pre><code>SELECT a AS id, b AS next_id, (b - a) -1 AS missing_inbetween
  FROM
    (
      SELECT a1.position_id AS a, MIN(a2.position_id) AS b
      FROM playerposition  AS a1
        LEFT JOIN playerposition AS a2 ON a2.position_id &gt; a1.position_id
      WHERE a1.position_id &lt;= 14
      GROUP BY a1.position_id
    ) AS tab
  
  WHERE
    b &gt; a + 1
  </code></pre>
  
  <p>I still need help with the <code>INSERT</code> part though</p>