title: Insert a greatest value (max+1)
body: <p>I have a table (<code>foo</code>) where I already have a PK on <code>id</code>: </p>
  
  <pre><code>id      name      rank
  -----------------------
   1      AAAA        2
   2      BBBB        1
  </code></pre>
  
  <p>I want to insert a new row where I know the values of column <code>id</code> and <code>name</code> and want <code>rank</code> to take a value greater than any other  value in the same column in preceding rows (similar to what <code>auto_increment</code> does for us).</p>
  
  <p>i.e. if I were to add a row with <code>value</code> = <code>CCCC</code>, the <code>rank</code> column should have a value <code>3</code>. I need to do this in a compound statement if possible. I tried the following which does not work.</p>
  
  <pre><code>insert into foo (`name`, `rank`)
  values ('CCCC', (select max(`rank`) from `foo`))
  </code></pre>
  
  <p>Which gives me the following error:</p>
  
  <pre><code>You can't specify target table 'foo' for update in FROM clause
  </code></pre>
  
  <p><strong>Note:</strong> I would ideally like to have the <code>rank</code> column as an <code>auto_increment</code> field, but apparently that's not allowed either, since I already have a PK.</p>
  
  <p><strong>PS:</strong> I need to be able to execute this statement from PHP without using stored procedures.</p>