Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2008

How to remove duplicate rows in SQL Server 2008 when no key is present

0.00/5 (No votes)
2 Sep 2011CPOL 15.1K  
Find out the number of duplicates in the table:select count(*), VISTX_ST_FIPS,VISTX_CTY_FIPS,VISTX_TC from TR00.NV_STARTER_INDEXgroup by VISTX_ST_FIPS,VISTX_CTY_FIPS,VISTX_TC having count(*)>1--- Delele them using following statementDELETEFROM TR00.NV_STARTER_INDEXWHERE...

Find out the number of duplicates in the table:


SQL
select count(*), VISTX_ST_FIPS,VISTX_CTY_FIPS,VISTX_TC 
from TR00.NV_STARTER_INDEX
group by 
VISTX_ST_FIPS,VISTX_CTY_FIPS,VISTX_TC 
having count(*)>1

--- Delele them using following statement
DELETE
FROM  TR00.NV_STARTER_INDEX
WHERE TR00.NV_STARTER_INDEX.%%physloc%%
      NOT IN (SELECT MIN(b.%%physloc%%)
              FROM   TR00.NV_STARTER_INDEX b
              GROUP BY VISTX_ST_FIPS, VISTX_CTY_FIPS, VISTX_TC)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)