Deleting duplicate records

ninel · June 14th, 2006, 11:43 AM

I have a table tmPunchtimeSummary which contains a sum of employee's hours
per day. The table contains some duplicates.

Code:

CREATE TABLE [tmPunchtimeSummary] 
(
[iTmPunchTimeSummaryId] [int] IDENTITY (1, 1) NOT NULL ,
[sCalldate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sEmployeeId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dTotalHrs] [decimal](18, 4) NULL 
) ON [PRIMARY]

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2468', 8.0)

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1357', 9.0)

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5)

INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5

How can I write a delete statement to only delete the duplicates which in
this case would be the 1st and 5th records?

Thanks,
Ninel

som_dutt · June 14th, 2006, 12:19 PM

Hi Ninel!

I don't think we have any delete statement which can be applied in such cases. One thing you can do -

(1) use select * into statement and create a new table.
select distinct * into newtable from oldtable
(2) Drop oldtable
(3) Use sp_rename for new table.

But, problem is - You will lose all Foreign keys, Rules and Defaults. So in that case, other method is to use a stored procedure.
If you need that stored procedure, mail me at - [email protected].

- Som Dutt

robprell · June 14th, 2006, 01:53 PM

you can fix it with a curser and only loose foreing key info (if you have any) on the rows you delete.

Peso · June 14th, 2006, 02:12 PM

Michael Valentine Jones wrote this for you:

select * from tmPunchtimeSummary

delete from a
from
    tmPunchtimeSummary a
    join
    (
    select
        sCalldate,
        sEmployeeId,
        dTotalHrs,
        iTmPunchTimeSummaryId = max(iTmPunchTimeSummaryId)
    from
        tmPunchtimeSummary
    group by
        sCalldate,
        sEmployeeId,
        dTotalHrs
    having
        count(*) <> 1
    ) b
    on
    a.sCalldate = b.sCalldate and
    a.sEmployeeId = b.sEmployeeId and
    a.dTotalHrs = b.dTotalHrs and
    a.iTmPunchTimeSummaryId <> b.iTmPunchTimeSummaryId

select * from tmPunchtimeSummary