Sunday, August 09, 2015

SQL - How To Find Duplicate Record in Database

There are some methods we can do to query duplicated record in the database, here the simple way to query them as well. For example you want to find all email addresses in a table that exist more than once:

    SELECT email,

    COUNT(email) AS NumOccurrences

    FROM users

    GROUP BY email

    HAVING ( COUNT(email) > 1 )

You could also use this technique to find rows that occur exactly once:

    SELECT email

    FROM users

    GROUP BY email

    HAVING ( COUNT(email) = 1 )< /FONT >

Then, how to query duplicate record with multiple column? The following SQL script example shows how to identify duplicate rows in a data table with multiple column, here the solution:

    SELECT name, email, address, count(*)

    FROM users

    GROUP BY name, email, address

    HAVING count(*) > 1

0 comments: