assistance identifying duplicate data

[iwm]

I have a table that contains funding sources. It has recently been brought to my attention that there are duplicate entries in the funding source table.

EXAMPLE
fundName: AAA, fundNumber: 123-456-7890, departmentID: 1

fundName: ZZZ, fundNumber: 123-456-7890, departmentID: 746

As you can see fund “AAA”, and fund “ZZZ” have been assigned the same fundNumber. Both funds are currently active.

I need to write an update statement, which identifies duplicate fundNumbers, and inactivates the duplicates which reside in a specific department. How do I identify the duplicates fundNumbers?

Thanks in advance for your help!

Ingrid

 

[imex]

Try something like this:

with CTE_D as
(
    select fundNumber
    from MyTable
    group by fundNumber
    having Count(*) > 1
)

update t
set MyColumn = 'false'
from MyTable as t
inner join CTE_D as c
    on c.fundNumber = t.fundNumber
where departmentID = 123

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[drlex]

May I make a suggestion, based on the OP?

with CTE_D as
(
    select fundNumber
    from [COLOR=#EF2929](select distinct fundname, fundnumber from [/color]MyTable [COLOR=#EF2929]) T1[/color]
    group by fundNumber
    having Count(*) > 1
)
..

This would ensure that only multiple names and numbers are returned, rather than every fundnumber from MyTable with more that one department (or other field) entry.

soi là, soi carré