CODEWITHSUNDEEP
mysqldatabaseduplicates
CastenettoA
CastenettoA

Reputation: 693

Duplicate entry 'n' for key 'primary', change ID when is duplicate

I have a MySQL table with a IDcolumn set as auto_increment. The table already contains some values.

I need to import about 4000 new rows that in some case they have the same ID that already exist on the table, so MySQL returns this error:

Error Code: 1062. Duplicate entry '7' for key 'PRIMARY'

How can I deal with it?

Below I post a piece of code that concern the file that I need to import:

INSERT INTO `wp_posts`
     (`ID`,`post_author`,`post_date`,`post_date_gmt`,`post_content`,`post_title`,`post_excerpt`,`post_status`,...)
  VALUES
     (7,1,'2015-04-24 11:19:41'....);

[and so on for 4000 rows ]

What I need is that the ID value is the same changes automatically.

Upvotes: 0

Views: 1302

Answers (3)

Daniel Materka
Daniel Materka

Reputation: 1

As far as I understand your concern, you might use: 

INSERT INTO wp_post (ID,post_author,post_date,post_date_gmt,post_content,post_title,post_excerpt,post_status,...) VALUES (7,1,'2015-04-24 11:19:41'....) ON DUPLICATE KEY UPDATE ID = 0;

and check if id has auto_increment attribute.

Upvotes: 0

reaanb
reaanb

Reputation: 10065

If you'd like to update existing rows when the IDs match, try adding an ON DUPLICATE KEY UPDATE clause:

INSERT INTO wp_posts (ID, post_author, post_date, post_date_gmt, post_content,
                      post_title, post_excerpt, post_status, ...)
VALUES (7, 1, '2015-04-24 11:19:41', ...)
ON DUPLICATE KEY UPDATE
    post_author=VALUES(post_author), post_date=VALUES(post_date),
    post_date_gmt=VALUES(post_date_gmt), post_content=VALUES(post_content),
    post_title=VALUES(post_title), post_excerpt=VALUES(post_excerpt),
    post_status=VALUES(post_status), ...

Upvotes: 3

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

As you are saying that your ID field is autoincrement. So you don't need to force id column during insertion. your insert query will autogenerate non duplicate unique id's 

INSERT INTO wp_posts (post_author,post_date,post_date_gmt,post_content,post_title,post_excerpt,post_status,...) VALUES (1,'2015-04-24 11:19:41'....)

Upvotes: 0

Related Questions