phpMyAdmin export/import results in duplicate primary key error

Question

I want to globally replace all instances of my site url in the Wordpress MySQL database with a new site url. To do that, I'm using phpMyAdmin to export the database to a .sql file, then doing a global replace in a text editor, then using phpMyAdmin to import the .sql file.

During the import, I'm encountering a duplicate entry for primary key error. Trying to debug this, I exported the file, then imported the identical file, making no changes and I still get the same error.

I appreciate any help solving this.

--
-- Dumping data for table `wp_comments`
--
INSERT INTO  `wp_comments` 
  (`comment_ID`, `comment_post_ID`, `comment_author`, `comment_author_email`
  ,`comment_author_url`, `comment_author_IP`, `comment_date`, `comment_date_gmt` 
  ,`comment_content`, `comment_karma`, `comment_approved`, `comment_agent` 
  ,`comment_type`, `comment_parent`, `user_id`) 
VALUES (1, 1, 'Mr WordPress', ''
       ,'http://wordpress.org/', '', '2011-04-28 00:49:55', '2011-04-28 00:49:55'
       ,'Hi, this is a comment.<br />To delete a comment, 
         just log in and view the post&#039;s comments. 
         There you will have the option to edit or delete them.'
       , 0, 'post-trashed',  '',  '', 0, 0 ) ;

MySQL said: 

#1062 - Duplicate entry '1' for key 'PRIMARY' 

Answer 1

The original data is still in the database.

If you were working manually you'd send a UPDATE rather than INSERT query with the new values, but since you're doing a batch import, it may just be better to wipe the existing table clean before the import.

DELETE FROM `tblName`;

Be sure to back-up your data, first.

Answer 2

To avoid duplicates you have to use UPDATE instead of INSERT statements. To achieve this in phpMyAdmin, follow these steps:

1. Select your database in the tree.
2. OPTIONAL. Go to "Search" tab and search for string you want to replace in all tables. If string is found in several tables only, note their names. This will help to speed up process by updating only the tables which needs updating. This my be important if you have lot of data.
3. Go to "Export" tab.
4. In the "Export method:" select "Custom".
5. OPTIONAL. If you noted the tables which need updating in step 2 above, then in the "Table(s):" section, click "Unselect all" and then select only the tables which need to be updated.
6. Scroll down to "Data creation options" section, and in the drop box labeled "Function to use when dumping data:" select "UPDATE" (default is "INSERT").
7. Click "Go".
8. Open the downloaded SQL dump file.
9. IMPORTANT! Save the file with a new name for backup purposes before any changes are made.
10. Use Search & replace function of your editor to change what you want. Then save the file.
11. In phpMyAdmin go to "Import" tab.
12. In the "File to import:" section click the "Choose file" button and browse for the edited file. Click GO

You are ready! To check if everything is OK, search the database second time (repeat step 2). You should not find any tables containing your old string.