Adobe Coldfusion Tip For Finding Duplicate Records In SQL Database

Finding Duplicates Records In SQL Table

Every so often, I find it necessary to query for duplicate records in a database. It doesn't come up in my every day work, but maybe every few months, I need to weed out duplicate records. Since this doesn't happen much, I always have to go digging through old code, or search online for some help. Well, I decided to write a hub about it.

This sample code is using Coldfusion to query a SQL table to display an html table of duplicate records. You could do the same, and email the results to someone. You could even set-up a cfschedule to periodically query a table and send out an email notification of dups.

However, you need the results, the heart of the issue, is the original query to pull the duplicates.

Sample Query For Duplicates

This query is finding duplicates based on a product sku and including the description and ID.

<CFQUERY NAME="Getdups" DATASOURCE="datasource name">
SELECT
product_sku,
product_description,
ID
FROM TABLENAME

WHERE (((TABLENAME.product_sku) In (SELECT [product_sku] FROM [TABLENAME] As Tmp GROUP BY [product_sku] HAVING Count(*)>1 And [product_sku] = [TABLENAME].[product_sku])) )
ORDER BY TABLENAME.product_sku

</cfquery>

Sample HTML Table Of Duplicate Query Results

Sample HTML output of query duplicates results, with mouse rollover that changes row color.

<center><table border=0 cellpadding=2 cellspacing=1 width="600">
<tr>
<td colspan=8 valign=top class="tdoutput"><b>DUPLICATES REPORT</b></td></tr>
<tr bgcolor="666666">
<td valign=top align=center class="tdarea"></td>
<td valign=top align=center class="tdarea">SKU</td>
<td valign=top align=center class="tdarea">DESCRIPTION</td>
</tr>
<cfoutput query="Getdups">
<TR bgcolor="e7e7e7" onMouseOver="this.bgColor='CCFF99'" onMouseOut="this.bgColor='e7e7e7'">
<td valign=top align=left class="tdoutput">#currentrow#).</td>
<td valign=top align=left class="tdoutput">#trim(product_sku)#</td>
<td valign=top align=left class="tdoutput">#trim(description)#</td>
</tr>
</cfoutput>
</table></center>

More by this Author

  • Gun Laws in Georgia
    38

    Firearms can be a very dangerous tool in the hands of the wrong person. Every state has their own gun laws and regulations. For the state of Georgia, the laws are pretty straight forward and easy to abide by. Before you...

  • How to Get Rid of Mice In and Around The House?
    5

    Until this year, the only time that I've had mice or rats in the house were when my daughter had pet mice and rats, and one of those times we did have one loose in the house. (Science fair project got loose.) Anyway,...

  • How to Build Wooden Roof Trusses
    23

    You may refer to a truss as the rafter, but it's basically the skeleton of the roof, carrying the weight of the frame and supporting the walls of the building. Trusses are very important to preventing the walls from...


Click to Rate This Article
working