CREATE TABLE `document_types_lookup` ( `id` INT(10) NOT NULL, `name` VARCHAR(100), `disabled` BIT DEFAULT'0' NOT NULL ); ALTER TABLE `document_types_lookup` ADD PRIMARY KEY (`id`);I needed to insert a few thousand rows, but since there is no auto-increment field for the
ID, I needed SQL that could increment ID for me - using SQL only, no script. I found a bug report (Allow INSERT...SELECT MAX(x)+1 from the same table) that had the below snippet that used
select (max(id)+1)to increment ID for an
insert into foo(lfd) select (max(lfd)+1) from foo;And while I could get that statement by itself to work (inserting only ID into a record), I could not not adapt it to add the rest of the record values.
insert into document_types_lookup(id, name, disabled) values (select (max(id)+1) from document_types_lookup, 'a name', false);I always got the below error.
Executing:So, my work-around is to use two statements instead of one: an insert followed by an update, as below.
insert into document_types_lookup(id, name, disabled) values (select (max(id)+1) from document_types_lookup, 'a name', false)
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select (max(id)+1) from document_types_lookup, 'a name', false)' at line 1
[Error Code: 1064]
[SQL State: 42000]
insert into document_types_lookup(id) select (max(id)+1) from document_types_lookup; update document_types_lookup set name = 'CORP_HMN_000_CA_', disabled = false where name is null;OK, see any problems with this script? You should - it's a dangerous script for two big reasons. Firstly, it isn't thread safe, i.e. using
select (max(id)+1)in an insert is dangerous if there are multiple people updating the table at the same time. You have no guarantee that between the time you have selected
max ID + 1and the time the insert actually happens, a new record might have been inserted by someone else, meaning that when your insert statement finally hits, your
max ID + 1will not be unique anymore. You can solve this by locking the table before your insert and releasing it afterwards. The second reason that this is a dangerous script is because it assumes there will only ever be one record where
name is null; it does this as I could think of no other way to determine what record I just inserted.
So, this script works only because a) I knew I was the only person who would be updating the table at that time and b) I knew
namewould have a value for all records other than the one I had just inserted.