Ignore duplicate key error in MySQL on INSERT

问题:

I am trying to duplicate a row in my table, into another table. There query looks like this

INSERT INTO `quote_block_arc` (`id`,`quote_id`,`name`,`description`,`price`,`hours`,`days`,`total_hours`,`is_print`,`amends`) SELECT `id`,`quote_id`,`name`,`description`,`price`,`hours`,`days`,`total_hours`,`is_print`,`amends` FROM `quote_block` WHERE `quote_id` = '41'

However, it failing saving there is a duplicate key for this row, is there a way to ignore that warning and run the query?

回答1:

This error occurs because you have specified one of the columns of your table to be UNIQUE. You cannot have 2 rows with the same value for this column. If you want to replace the existing row instead, use REPLACE instead of INSERT. If you really want rows containing the same value for the column, remove the UNIQUE modifier from that column.

Using INSERT IGNORE as described in some of the other answers will prevent the error being issued, but will not update the table.

回答2:

You can perform INSERT IGNORE, or INSERT ... ON DUPLICATE KEY UPDATE Another way is to check result of the query, if it's false - check the error, and if the error code is 1062 (duplicate entry) to continue execution as if there were no error.

回答3:

try

INSERT IGNORE INTO `quote_block_arc` (`id`,`quote_id`,`name`,`description`,`price`,`hours`,`days`,`total_hours`,`is_print`,`amends`) SELECT `id`,`quote_id`,`name`,`description`,`price`,`hours`,`days`,`total_hours`,`is_print`,`amends` FROM `quote_block` WHERE `quote_id` = '41'

回答4:

Having a duplicate row in a table with a unique/primary constraint would violate its integrity. You should check what the key is and confirm if you actually need to copy it. For example, AUTO_INCREMENT primary keys are traditionally not inserted because the database will fill that for you automatically. I'd check the destination table quote_block_arc if id is AUTO_INCREMENT. IF it is, remove it from the INSERT and let MySQL insert it by itself.

Using INSERT IGNORE will result in your row not being inserted. Use REPLACE if you want to overwrite the duplicate key, but I do not recommend it over the method I've described earlier.

回答5:

@cmmi - I'm not having duplicates, I'm inserting data from 1 table into another.

I recommend you get the highest id from the target quote_block_arc, you then add that to the id in your SELECT subquery. That way, you know how to update any other related entries in other tables that reference those rows. If you were to let the RDBMS assign new ids for you, all relation would be lost.