title: insert into table select max(column_name)+1
body: <p>I have this mysql table built like this:</p>
  
  <pre><code>CREATE TABLE `posts` (
      `post_id` INT(10) NOT NULL AUTO_INCREMENT,
      `post_user_id` INT(10) NOT NULL DEFAULT '0',
      `gen_id` INT(10) NOT NULL DEFAULT '0',
      PRIMARY KEY (`post_user_id`, `post_id`)
  )
  COLLATE='utf8_general_ci'
  ENGINE=MyISAM;
  </code></pre>
  
  <p>When I do:</p>
  
  <pre><code>insert into posts (post_user_id) values (1);
  insert into posts (post_user_id) values (1);
  insert into posts (post_user_id) values (2);
  insert into posts (post_user_id) values (1);
  select * from posts;
  </code></pre>
  
  <p>I get:</p>
  
  <pre><code>post_id | post_user_id | gen_id
  1                  1     0
  2                  1     0
  1                  2     0
  3                  1     0
  </code></pre>
  
  <p>A unique post_id is generated for each unique user.</p>
  
  <p>I need the gen_id column to be 1 2 3 4 5 6 etc. How can I increment this column when I do an insert. I tried the one below, but it won't work. What's the right way to do this?</p>
  
  <pre><code>insert into posts (post_user_id,gen_id) values (1,select max(gen_id)+1 from posts);
  //Select the highest gen_id and add 1 to it.
  </code></pre>