So something like this:

/*

1 5 City A Delete

3 6 City B Delete

1 7 City C Delete

1 8 City E Keep

2 8 City E Delete

3 8 City E Delete

1 9 City F Keep

2 9 City F Delete

1 10 City G Keep

1 10 City G Delete

1 11 City H Keep

2 11 City H Delete

3 11 City H Delete

4 11 City H Delete

*/

create table dbo.City(

CityId int identity(1,1),

CityName varchar(10),

DateDeleted datetime null

);

insert into dbo.City(CityName)

values

('City A'),

('City B'),

('City C'),

('City E'),

('City E'),

('City E'),

('City F'),

('City F'),

('City G'),

('City G'),

('City H'),

('City H'),

('City H'),

('City H');

with BaseCity as (

select

CityId,

CityName,

rn = row_number() over (partition by CityName order by CityId),

cnt = count(*) over (partition by CityName)

from

dbo.City

)

select

*

from

BaseCity

where

rn > 1 or

cnt = 1;

go

with BaseCity as (

select

CityId,

CityName,

DateDeleted,

rn = row_number() over (partition by CityName order by CityId),

cnt = count(*) over (partition by CityName)

from

dbo.City

)

update BaseCity set

DateDeleted = getdate()

where

rn > 1 or

cnt = 1;

go

select * from dbo.City;

go

drop table dbo.City;

go