Here's the problem: I moved to a new social profile system for one of my sites. In the move, certain profile fields in the DB were duplicated. Here's a basic structure of a table I want to fix ID...............user_id..........field_id..........value 85772..........5703..............2..................male 85773..........5703..............2..................male 85774..........5703..............10................Madrid 85775..........5703..............10................Madrid Above there are four records, but only two need to be in the db. The field ID's #2 and #10 for this user_id of 5703 are duplicated. ID is an autoincrement and is not important. So I need a script or sql command or way to do this in phpmyadmin to remove the duplicate fields for each user. Any suggestions would be appreciated. 1. Search fields and remove duplicate tables which have the same field_id
Hi, The following query will select data without duplicate "field_id" SELECT * FROM `table_name` GROUP BY `field_id` Code (markup): then you can click "Export" icon in "phpMyAdmin" and export checked row. Other variant. Create temporal table with the same table structure and insert data from currently table. INSERT INTO `temporal_table_name`(`user_id`, `field_id`,`value`) SELECT `user_id`, `field_id`,`value` FROM `table_name` GROUP BY `field_id` Code (markup):
First group by user_id and then by field_id. Alternate to this, you can add a unique key on user_id, field_id and get the problem solved easily. ALTER IGNORE TABLE `table_name` ADD UNIQUE INDEX `idx_unique` (`user_id`, `field_id`);