INSERT INTO...SELECT - Primary key conflict - error #1062

Question

I have a database: groupofficecom has two tables:

cal_events: id(Primary key), name, start_time, description,....
cf_cal_events: model_id (Primary key), col_1, col_2, col_3,....

I'm trying to execute the following code:

INSERT INTO groupofficecom.cf_cal_events (model_id,col_1,col_2,....)
SELECT groupofficecom.cal_events.ID, '0' AS col_1, '' AS col_2,....
FROM groupofficecom.cal_events

But it keeps giving me error #1062 - Duplicate entry '155' ('155' is the 'id' from cal_events) for key 'PRIMARY'

I want the primary key model_id to be the same value as id in cal_events because the table cf_cal_events is just complementary fields for cal_events (this is a program, so I can't change its database, it'll be gone on the first update)

Thank you guys!

Id number 155 is already exist in table before your insertion query — Sadikhasan, Jun 17 '14 at 09:42
Did you check to see if there is value '155' in the cf_cal_events table? — Dusan, Jun 17 '14 at 09:43
cf_cal_events is empty! That's the problem, I guess the insertion has some duplicate I can't find, there is no other logical explanation — rand, Jun 17 '14 at 09:50

score 1 · Answer 1 · edited May 23 '17 at 10:33

1

This means there already is an entry with that id in the target table. First, check how this can be.

Then, use one of the solutions described here as is appropriate: "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE" i.e. UPDATE or IGNORE.

You should use an ORDER BY with the select you have and the solution above to choose which entries get ignored (all but the first entry with IGNORE).

It is also possible that you want something different entirely, i.e. to use an UPDATE statement instead of an INSERT statement.

edited May 23 '17 at 10:33

Community

1
1

answered Jun 17 '14 at 09:43

Adder

5,708
1
28
56

Also it's a very good article on this theme: http://www.xaprb.com/blog/2006/02/21/flexible-insert-and-update-in-mysql/ – MikkaRin Jun 17 '14 at 09:46
No, the target table is empty, the problem must be in the insertion, I'll try the `INSERT ON DUPLICATE KEY UPDATE` because it's not an `UPDATE` – rand Jun 17 '14 at 09:53

score 1 · Accepted Answer · answered Jun 19 '14 at 15:06

In fact I found a very good function, it's very similar to the INSERT but smarter:

REPLACE INTO database (column_1, column_2)
SELECT source_column1, 'value' AS column2
FROM table;

Or:

REPLACE INTO database (column_1, column_2)
VALUES ('value1', 'value2')
FROM table;

Works like magic!

It inserts new items to the destination table, and if it finds a row with the same primary key value, it erases it and re-inserts the new value (it works great for updating a table from another one)

I hope this solves your problem like it solved mine ;)

INSERT INTO...SELECT - Primary key conflict - error #1062

2 Answers2