Category: mysql

Autoincrement insert-select on a non-autoincrement column

There are often times when you need to insert into an existing table that contains data with a unique numeric field. If you created the field as AUTOINCREMENT, everything is fine. However, in my case, I did not because I generate sqlite tables from my MySql database. So, I have many tables that look like this:

drop table concentration_card if exists;

create table concentration_card (
	card_id int not null default 0,
	/*
      ...
	*/
);

create unique index idx0 on concentration_card (card_id);

I came across this answer on StackOverFlow, which I used to solve my problem: http://stackoverflow.com/a/3127004/2122052

First we need the maximum value from the desired field:

select max(concentration_card); -- We don't need a +1 here because we increment in SELECT

Then set the initial @curRow value and create the insert-select query:

 --insert into concentration_card
  SELECT @curRow := @curRow + 1 as card_id, 0, p.dict_id, 
      p.dict_id, 'Verbs' as name, 0 as inactive,
      '' as comments, 6 as major_group_id
  FROM picture_dictionary p
  JOIN    (SELECT @curRow := 1526) r
  where p.word_group = 'Verbs'
   and p.`dict_id` not in (  
 select word1_id
 from concentration_card
 where name in ('Basic Verbs', 'Common Verbs')
 );