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">
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
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">
<td colspan=8 valign=top class="tdoutput"><b>DUPLICATES REPORT</b></td></tr>
<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 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>
More by this Author
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...
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,...
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...