65.9K
CodeProject is changing. Read more.
Home

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

emptyStarIconemptyStarIconemptyStarIconemptyStarIconemptyStarIcon

0/5 (0 vote)

Sep 2, 2011

CPOL
viewsIcon

15140

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:

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)