DELETE duplicate records where specific fields are equal

[OldWest]

I have been hammering at this for about 2 hours, searched Google to high hell, and have been unsuccessful getting the result I need  :'(

 

Any tips welcome please!

 

I have 1 table: sys_city_dev_1

I need to remove all records WHERE cityName AND Mid are equal. But of course keep all existing records so as to only remove duplicates with that criteria.

 

I thought this would do the trick:

 

CREATE TABLE sys_city_dev_2 AS
SELECT * FROM sys_city_dev_1 WHERE 1 GROUP BY cityName

 

, and another query I wrote froze my database up multiple times!

 

DELETE FROM sys_city_dev_1
USING  sys_city_dev_1, sys_city_dev_1 AS a
WHERE (NOT sys_city_dev_1.Mid = a.Mid)
AND ( sys_city_dev_1.cityName = a.cityName)

 

I've tried many variations of the aboves with no luck at all!

 

 

Anyone? SOS

[Pikachu2000]

Perhaps this is what you mean?

DELETE FROM `table` WHERE `field1` = `field2`

[Pikachu2000]

Nevermind the above, I just noticed you only want to delete where the fields are equal, but not if it's the only record. Can you maybe provide some sample data?

[OldWest]

Here is the structure:

 

--
-- Table structure for table `sys_city_dev_1`
--

CREATE TABLE IF NOT EXISTS `sys_city_dev_1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Mid` int(11) NOT NULL DEFAULT '0',
  `cityName` varchar(30) NOT NULL DEFAULT '',
  `forder` int(4) NOT NULL DEFAULT '0',
  `disdplay` int(4) NOT NULL DEFAULT '0',
  `cid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `sys_city_dev_1`
--

[Pikachu2000]

And the only requirement for the record to be deleted is that it isn't the only record in the db with those two fields equal?

[OldWest]

And the only requirement for the record to be deleted is that it isn't the only record in the db with those two fields equal?

 

Exactly.

[OldWest]

I might have found something that works, but I think the issue lies now in my MySQL server timing out (have about 90,000 records I'm scanning through) when I run the query... It disconnects after about 3 mins with:

 

DELETE FROM sys_city_dev_1_500
USING sys_city_dev_1_500, sys_city_dev_1_500 AS vtable
WHERE (sys_city_dev_1_500.Mid = vtable.Mid)
AND (sys_city_dev_1_500.cityName = vtable.cityName)

 

I need to see if I can change the MySQL time out disconnect issue..

 

Anyone any ideas? Back to hacking at it...

 

[Pikachu2000]

This may not be very efficient, but it should work, and let you inspect the IDs of the records that are flagged to be deleted before actually deleting them. In any event, if this is actual production data, test and double test it on a development box first.

 

Select and loop through all records where the fields in question are equal.

For each record, if the Mid field's value is the same as the previous record, store the PK ID in an array.

Store the value of the Mid field in a variable.

 

Then use the values in that array to delete the appropriate records, either with a foreach loop, or DELETE . . . WHERE . . . IN() syntax

 

So:

$query = "SELECT ID, Mid FROM sys_city_dev_1 WHERE cityName = Mid";
$result = mysql_query( $query );
$city = '';
$delete = array();
$while( $array = mysql_fetch_assoc($result) ) {
     if( $array['Mid'] == $city ) {
          $delete[] = $array['ID'];
          $city = $array['Mid'];
     }
}