0

I am trying to insert into one of my tables (from the same table). I need to change 1 of the values, so I tried to insert with select as answered here. I have an auto increment pk, and would want the newly inserted pk to continue the count. so inserting would result into adding 1 to the pk.

INSERT INTO test.pp_publication_info
(id,
publication_fk,
template_fk,
allow_excel_upload)
SELECT 
id, 55, template_fk, allow_excel_upload
FROM pp_publication_info where id = "1";

I get a 1062 error, which is a duplicate entry for 1 for KEY PRIMARY. I would have though that because of the AI it would +1 the ID column. SO I checked to see if I could add a ON DUPLICATE KEY UPDATE id. Unfortunately it didn't matter where I put that in the query.

So in short: can I INSERT this way, with select whilst holding the AI function? If so, where am I going wrong and if not, are there any any alternatives?

Addition: create statement of my table:

CREATE TABLE `pp_publication_info` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`publication_fk` int(10) unsigned NOT NULL,
`template_fk` int(10) unsigned DEFAULT NULL,
`allow_excel_upload` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
Community
  • 1
  • 1
Nina
  • 99
  • 12

1 Answers1

1

Your id column is AUTO_INCREMENT:

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

This means the database will generate an identifier for you when the record is inserted. So simply don't insert a value into that field. It will be populated automatically.

So something like this:

INSERT INTO test.pp_publication_info
  (publication_fk,
   template_fk,
   allow_excel_upload)
SELECT 
  55, template_fk, allow_excel_upload
FROM pp_publication_info where id = 1;

After the INSERT is executed, you'll find that the id column of any new record(s) has been populated with the next value(s) in the sequence. (Barring any gaps in the sequence, which could happen for any number of reasons.)

David
  • 208,112
  • 36
  • 198
  • 279