>>>OK, here's my ever so often dumb Friday afternoon question.
>>>
>>>I ran the Sedna upsizing wizard to upsize a VFP database to SQL Server 2012 and it managed to create duplicate records, so I now need to delete those that are duplicates. There are only a few of them so I was going to manually do it in SSMS, but of course that won't work because there is no way to uniquely identify the record I am trying to delete.
>>>
>>>Any suggestions on how to do this?
>>
>>Did you create complete duplicates or some fields are different?
>>
>>You may try:
>>
>>;with cte as (select *, row_number() over (partition by Column1, Column2, Column3 order by Column1) as Rn
>>from TableWithDups)
>>
>>delete from cte where Rn > 1
>
>the entire record was duplicated (don't ask me how). I tried this:
>
>
;with cte as (select *, row_number() over (partition by nav_list, nav_item, nav_command, nav_image, nav_caption, nav_securityid, nav_active order by nav_list) as Rn
>from navigator)
>
>just to see what it returned, but I get a syntax error near ')'
You need to add
select * from cte
after defining the cte to see what is returned.
If it's not broken, fix it until it is.
My Blog