CODEWITHSUNDEEP
mysqlsqlprimary-keyinsert-into
rand
rand

Reputation: 183

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

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!

Upvotes: 0

Views: 2439

Answers (2)

rand
rand

Reputation: 183

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 ;)

Upvotes: 1

Adder
Adder

Reputation: 5868

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.

Upvotes: 1

Related Questions