Find duplicate emails in MySql and delete certain ones based on conditions

Question

I see a lot of ways to find duplicate records and delete them but I could not find the one I am looking for i.e. to delete them based on certain condition.

The first SQL code below taken from another post works very well in locating duplicates. However, I am having problem withe the second part.

<cfquery datasource="mydatabase" name="duplist">
SELECT  a.*, b.totalCount AS Duplicate
FROM    mytable a
        INNER JOIN
        (
            SELECT  email, COUNT(*) totalCount
            FROM    mytable
            GROUP   BY email
            HAVING  COUNT(*) > 1
        ) b ON a.email = b.email
</cfquery>

<cfoutput query="duplist">
    <CFQUERY DATASOURCE="mydatabase" name="dlist">
    SELECT * FROM mytable
    WHERE userid = '#userid#'
    AND activedate is null
    </CFQUERY>
</cfoutput> 

<cfoutput>
Total To Be Deleted: #dlist.recordcount#
</cfoutput>

I am trying use SELECT (before replace it with DELETE) only those duplicates user emails that do not have an "activedate" based on the output query "duplist" with duplicated email records. I am only getting one record (in #dlist.recordcount#) which is the last record! Isn't the query supposed to loop through them all?

EDITED:

After some feedback and checking the recordcount wasn't counting correctly because it is outside the loop. It will work in retrieving those records without startdate. However, now the real problem is in the following scenario:

ID   USERID   EMAIL          STARTDATE
1    user1    test@test.com 
2    user2    test@test.com  11/01/2014
3    user3    test@test.com 
4    user4    test@test.com  11/02/2014 
5    user5    me@mydomain.com
6    user6    me@mydomain.com

Running through the above code will list these 6 records of duplicate emails. The second query will select(delete) those without startdate i.e. Record 1, 3, 5 and 6. Two problems:

1. Record 1 and 3 will be deleted, but 2 and 4 are still duplicates
2. Record 5 and 6 will be deleted and I am left with no user with the email. I need to keep one of them.

How can the codes be modified to deal with the scenarios? I want to keep record #4 and #6 because 4 is newer than 2 and 6 is newer than 6.

4    user4    test@test.com  11/02/2014 
6    user6    me@mydomain.com

Answer 1

You can use IN clause in your query. But first you need to create a list of userId's that you are going to pass into your second query. You can do that using valueList like this

<cfset userIdList = valueList(duplist.userId) />

and then pass that list in your query like this

<CFQUERY DATASOURCE="mydatabase" name="dlist">
    SELECT * FROM myTable WHERE userId IN (#userIdList#) AND activeDate is null
<CFQUERY>

Answer 2

This can be done in single query also

<CFQUERY DATASOURCE="mydatabase" name="dlist">
 SELECT * FROM myTable 
 WHERE userId IN (SELECT userId
    FROM    mytable 
    GROUP   BY email
    HAVING  COUNT(email) > 1
   ) 
   AND activeDate is null
<CFQUERY>

Answer 3

This is your original query:

SELECT  a.*, b.totalCount AS Duplicate
FROM    mytable a
    INNER JOIN
    (
        SELECT  email, COUNT(*) totalCount
        FROM    mytable
        GROUP   BY email
        HAVING  COUNT(*) > 1
    ) b ON a.email = b.email

All you need to do is add:

where a.activeDate is null

and you have your records. Then once you have seen them, replace your select clause with the word "delete"

Edit Starts Here

Since this will still leave duplicates, after you do the above, do something like this:

select id, email -- can be changed to delete after
from myTable deleteFrom join myTable keep on deleteFrom.email = keep.email
where deleteFrom.id < keep.id

That will keep the newest record.

Answer 4

You can use the Rank() function to rank the data and delete the rows which are not ranked 1.

Oracle equivalent of the Rank() query to select the #4 and #6 data:

SELECT * FROM
(
SELECT USERNAME, EMAIL, START_DATE, 
        RANK() OVER (PARTITION BY EMAIL ORDER BY START_DATE DESC NULLS LAST, ROWNUM DESC) AS RANK
FROM TEMP_SOL
)
WHERE RANK = 1

Mysql equivalent can be derived using this link Rank function in MySQL with Order By clause

Edit : A non-rank solution can be something like:

First get all the duplicate emails

<cfquery name="dupEmail" datasource="XXX">
SELECT EMAIL
FROM TEMP_SOL
GROUP BY EMAIL
HAVING COUNT(*) > 1
</cfquery>

Loop through dupEmail and

1. select the username which doesn't have to be deleted.

2. Select/Delete the usernames not equal to the above username having the same emailid.

   <cfloop query="dupEmail">
      <cfquery name="UserToRetain" datasource="XXX">
          SELECT USERNAME FROM TEMP_SOL
          WHERE EMAIL = '#dupEmail.Email#'
          ORDER BY START_DATE DESC NULLS LAST, ROWNUM DESC
          LIMIT 1 
     </cfquery>
   
     <cfquery name="DeleteUsers" datasource="XXX">
            SELECT * FROM TEMP_SOL
            WHERE USERNAME <> '#UserToRetain.USERNAME#' AND EMAIL='#dupEmail.Email#'
     </cfquery>
   
   </cfloop>
   

Note: The code above hasn't been tested for and the queries may not run on mysql.