f2sTvRMj

1CREATE TABLE users
2(
3  id int(11) NOT NULL AUTO_INCREMENT, 
4  email varchar(200) DEFAULT NULL, 
5  alt_email varchar(200) DEFAULT NULL, 
6  mobile varchar(100) DEFAULT NULL, 
7  alt_mobile varchar(100) DEFAULT NULL, 
8  PRIMARY KEY (id), 
9  KEY mobile (mobile), 
10  KEY email (email)
11) ENGINE=InnoDB; 
12     
13ALTER IGNORE TABLE users
14    ADD UNIQUE(email, alt_email, mobile and alt_mobile); 
15     
16CREATE TABLE new LIKE users;
17ALTER TABLE new ADD UNIQUE(email, alt_email, mobile and alt_mobile);
18INSERT INTO new
19    SELECT * FROM users
20        GROUP BY (email, alt_email, mobile and alt_mobile);
21check the contents of `new` to see if it worked correctly, esp for NULLs
22RENAME TABLE users TO old, new to users;
23DROP TABLE old; 
24     
25SELECT 
26    t1.id, 
27    t2.id, 
28    t1.cdate, 
29    t2.cdate, 
30    t1.email, 
31    t2.email, 
32    t1.alt_email, 
33    t2.alt_email,
34    t1.mobile, 
35    t2.mobile, 
36    t1.alt_mobile, 
37    t2.alt_mobile
38    # Include whatever other columns may be helpful
39FROM
40    # Or users_test if it exists
41    users t1
42    JOIN users t2 ON (
43        # We want t2 to always contain a duplicate but older record
44        t1.cdate > t2.cdate 
45        AND
46        t1.email = t2.email
47        AND
48        t1.alt_email = t2.alt_email
49        AND
50        t1.mobile = t2.mobile
51        AND
52        t1.alt_mobile = t2.alt_mobile
53    )
54; 
55     
56DELETE FROM users WHERE id in (/* list of t2.id values here */); 
57     
58INSERT INTO users 
59SELECT * FROM users_old
60ON DUPLICATE KEY UPDATE 
61some_column = VALUES(some_column), 
62some_other_column = VALUES(some_other_column), …
63;