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





0/5 (0 vote)
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)