How to Delete Duplicate Customer Rows in Magento Using Query

customerimportmagento-1.9PHP

I was imported the customer in Magento via pro-grammatically,actually i having nearly 320000 records,also in that having some duplicate records. the duplication is based on firstname,lastname and email.

So i like to remove the duplicate customer from customer table, i search in google to find the solution, but not yet get any positive result.

I found this query-

select d1.id FROM demo d1, demo d2 WHERE d1.firstname = d2.firstname AND d1.id > d2.id;

But i dont know how to write in magento

if you have any idea regarding my requirement means, your idea will help me much more,i am waiting for your positive response.

Best Answer

I don't know (yet) an sql command to get you what you need, mainly because the customer entity is EAV and the firstname, lastname and e-mail are kept in a separate table on separate line, but here is what you can do to identify the ids that need to be deleted.

First and most important...backup your database in case I'm wrong.

Then, and only then, you can create a small script. Create the file cleanup.php located in the same folder as index.php.

<?php 
error_reporting(E_ALL | E_STRICT); 
define('MAGENTO_ROOT', getcwd()); 
$mageFilename = MAGENTO_ROOT . '/app/Mage.php'; 
require_once $mageFilename; 
Mage::setIsDeveloperMode(true); 
ini_set('display_errors', 1); 
umask(0);
//instantiate the app model
Mage::app(); 

$keyAttributes = array('firstname', 'lastname', 'email');

$collection = Mage::getModel('customer/customer')->getCollection()
                  ->addAttributeToSelect($keyAttributes)
                  ->addAttributeToSort('entity_id', 'ASC');

$existingKeys = array();
$idsToDelete = array();
$glue = '####';

foreach ($collection as $customer) {
    $keys = array();
    //build an array with the fname, lname and email
    foreach ($keyAttributes as $attribute) {
        $keys[] = $customer->getData($attribute);
    }
    //implode all the values in a single string
    $key = implode($glue, $keys); // variable spelling is changed to keys
    //if this string was not found in the past, mark it as found
    if (!isset($existingKeys[$key])) {
        $existingKeys[$key] = 1;
    } else { //otherwise, the customer needs to be deleted
        $idsToDelete[] = $customer->getId();
    }
}
//generate some sql queries that need to be executed
//delete 50 at a time
$chunkSize = 50;
foreach (array_chunk($idsToDelete, $chunkSize) as $chunk) {
    echo "DELETE FROM customer_entity WHERE entity_id IN (".implode(',', $chunk).");<br />";
}

Now you can call in the browser magento_root/cleanup.php and you should see a set of delete statements.
You can execute them on your db.
These will delete the records from customer_entity table, and the rest of the values associated to those customers will cascade nicely.

Note: the code is not tested but this is how I would do it.

Related Topic