Delete duplicate row SQLQuery

電腦小知識

–loop 幾多

SET ROWCOUNT 0

–check and delete duplicate row

select * from TBL_ROLE_NAVIGATOR

select distinct * into wo from TBL_ROLE_NAVIGATOR

where C_PAGE_CODE in (select C_PAGE_CODE from TBL_ROLE_NAVIGATOR group by C_PAGE_CODE, C_ROLE_ID having COUNT(*) > 1)

order by C_PAGE_CODE

select * from wo

delete from TBL_ROLE_NAVIGATOR where C_PAGE_CODE in (select C_PAGE_CODE from wo)

insert TBL_ROLE_NAVIGATOR select * from wo

drop table wo

, , ,