Muhammad Muazzam Muhammad Muazzam - 22 days ago 6
MySQL Question

insert record from another table with auto increment column new table

I have to insert record from a table with

auto_increment
id field.

CREATE TEMPORARY TABLE tmptable SELECT *
FROM `wp_rg_lead_detail`
WHERE lead_id =59;
UPDATE tmptable SET lead_id =60 WHERE lead_id =59;
insert into `wp_rg_lead_detail` (`id`, `lead_id`, `form_id`, `field_number`, `value`)
select `id`, `lead_id`, `form_id`, `field_number`, `value`
from `tmptable`
WHERE lead_id =60;



Duplicate entry '1' for key 'PRIMARY'


I need auto_increment filed
id
in target table should increment by above query.

Answer Source

Just don't insert the ID, and let the autoincremenet property of the column handle it:

INSERT INTO `wp_rg_lead_detail` 
(`lead_id`, `form_id`, `field_number`, `value`) -- No Id!
SELECT `lead_id`, `form_id`, `field_number`, `value`
FROM   `tmptable`
WHERE  `lead_id` = 60;