MySQL INSERT with a Max() + 1 SubQuery

published by d.cochran on Tue, 11/03/2009 - 20:14

This was a pain in my ass to figure out so I figured I would post it here in case anyone else needs to insert a record using a Max() + 1 SubQuery.

I only use the Max()+1 SubQuery when the value I want to increase is not the primary key.

INSERT INTO table (row1, row2, row3) SELECT 1 + COALESCE((SELECT MAX(row1) FROM table), 0), 'value2', 'value3';

Comments

Permalink Submitted by Paul (not verified) on Fri, 03/26/2010 - 11:39

Thank you! I wasted an hour trying to do this before finding your site, sorted me out! Cheers

Permalink Submitted by juzek (not verified) on Wed, 07/20/2011 - 08:04

lovely! :)

Permalink Submitted by Srdjan (not verified) on Thu, 04/21/2011 - 08:35

Great article thank you very much, you just saved from same kind of pain..:)Thank you !Cheers from Belgrade

Permalink Submitted by Justin (not verified) on Mon, 06/27/2011 - 17:18

MySQL really needs to document this as an alternative. Excellent work.

Permalink Submitted by Anonymous (not verified) on Thu, 12/27/2012 - 07:09

Thanks a lot ! Simple and efficient ! Just what I wanted !

Permalink Submitted by George (not verified) on Tue, 02/05/2013 - 04:52

Hi, thanks a lot for the query. Saved me some time a nervs.greetings from Czech Republic ;-)