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


Click to Rate This Article
working