duplicated rows

Hello,

I have a table T1 with fields ID, F1, F2, F3, F4, F5, F6….

I need to find if there is duplicated rows based on F1, F2, F3 columns. If there is set F5=’minimum’ where ID is MIN(ID). So the smallest should be set as minimum. How can I do this in a stored procedure?

You can do something like below (assuming that ID column is unique):

update T1

set F5 = 'minimum'

where ID = (

select min(t.ID)

from T1

group by F1, F2, F3

having count(*) > 1

)

Else you can do below:

update t1

set F5 = 'minimum'

from T1 as t1

join (

select F1, F2, F3, min(t.ID) as min_id

from T1

group by F1, F2, F3

having count(*) > 1

) as t2

on t2.F1 = t1.F1 and t2.F2 = t1.F2 and t2.F3 = = t1.F3

where t2.min_id = t1.ID

|||Thanks, this helps a lot. It seems I need to mark all other rows as
NotMin. Is there a way to mark all the other duplicated rows as SET f5 =
'NotMinimum' if they are not Min(ID), there mgth be more than two rows
duplicated.|||

You can change the 2nd UPDATE stmt to:

update t1

set F5 = 'NotMinimum'

from T1 as t1

join (

select F1, F2, F3, min(t.ID) as min_id

from T1

group by F1, F2, F3

having count(*) > 1

) as t2

on t2.F1 = t1.F1 and t2.F2 = t1.F2 and t2.F3 = = t1.F3

where t2.min_id < t1.ID