How to Remove, Delete double records but still have the values intact.

First we get all the double records. In this case I have a table called "test" with 2 columns "tst_id char(26) DEFAULT newid(),tst_name varchar(50)".

Secondly we use the GROUP attribute in the <cfquery> tag to only get SINGLE records. In this query we declare a temporary table (MSSQL - memory only) and we insert the SINGLE records in that table

then we DELETE all the double items and after that insert all the single items back into the original table from the temporary table.

Leaves you with all the double items removed!! BUT BE CAREFULL, this method doesn't take into account that records may have a relation with other tables in the database...

<cfquery>
SELECT  tst_id,tst_name
FROM test
WHERE UPPER(tst_name) IN (
  SELECT UPPER(tst_name) as tst_name
  FROM test
  GROUP BY tst_name
  HAVING (COUNT(UPPER(tst_name)) > 1)
)
ORDER BY tst_name
</cfquery>

<cfsavecontent variable="sqlStmt">
  DECLARE @tmp TABLE (tst_id char(36),tst_name varchar(50))

  <!--- Only insert single items of the double records --->
  <cfoutput query="qDouble" group="tst_name">
    INSERT INTO @tmp VALUES ('#tst_id#','#tst_name#')
  </cfoutput>

  <!--- Delete ALL double records --->
  DELETE
  FROM test
  WHERE UPPER(tst_name) IN (
    SELECT UPPER(tst_name) as tst_name
    FROM test
    GROUP BY tst_name
    HAVING (COUNT(UPPER(tst_name)) > 1)
  )
 
  <!--- Insert the single records back in the database --->
  INSERT INTO test (tst_id,tst_name)
  SELECT tst_id,tst_name FROM @tmp
</cfsavecontent>

<!--- Execute the query --->
<cfquery>
  #PreserveSingleQuotes(sqlStmt)#
</cfquery>