How to delete duplicate MySQL records with duplicate field value (as ID)

For some reason i had duplicate entries in my WP database. The ID field in wp_users table was tempered with and was changed to Non Auto-Increment to insert records on the will. I deleted all ID=null records manually and then tried to change it to AUTO INCREMENT but it came up with Duplicate Entry error and then i found that it had duplicated/multiple entries with same IDs as well. Here’s the remedy i had to apply in order to delete those duplicate entries and change ID field to AUTO INCREMENT once again.

(Note: Backup you wp_users table to avoid any mis-happening.)

CREATE TABLE
bad_temp(ID bigint(20) unsigned NOT NULL,
user_login varchar(60) NOT NULL DEFAULT '',
user_pass varchar(64) NOT NULL DEFAULT '',
user_nicename varchar(50) NOT NULL DEFAULT '',
user_email varchar(100) NOT NULL DEFAULT '',
user_url varchar(100) NOT NULL DEFAULT '',
user_registered datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
user_activation_key varchar(60) NOT NULL DEFAULT '',
user_status int(11) NOT NULL DEFAULT '0',
display_name varchar(250) NOT NULL DEFAULT '');

INSERT INTO bad_temp(ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name) SELECT DISTINCT ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name FROM wp_users;

DELETE FROM wp_users;

INSERT INTO wp_users(ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name) SELECT ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name FROM bad_temp;

DROP TABLE bad_temp;

I hope that helps someone.

Leave a Reply

Your email address will not be published. Required fields are marked *