We help IT Professionals succeed at work.

Stored Procedure to delete duplicate records

mustish1
mustish1 asked
on
294 Views
Last Modified: 2012-06-27
Hi: How to i write a stored procedure so that it will first sum the Cur_Bal of all those Debtor_Id which are duplicate
according to clt_id and then delete the duplicate records from the table. This is the query to select the required data but i
dont know how to i write a procedure to update first the data and then delete the duplicates.

SELECT CLT_ID, Sum(CUR_BAL) AS SumOfCUR_BAL, Debtor_ID, Name1
FROM TableName
GROUP BY CLT_ID, Debtor_ID, Name1;

REQUIRED DATA
=============
CLT_ID     CUR_BAL         Debtor_ID       Name1
CERT02          525.0000      10448038      Beatrice Valdez
CERT02          330.1600      10448039      Beth Kerr
CERT02          612.8100      10448040      Beverly Lagler
CERT02          4800.0000      10448041      Bobby Sockwell
CERT02          230.0000      10448042      Brandy Brooks
CERT02          248.3900      10448043      Brandy Gibson
CERT02          550.9100      10448044      Brenda Brewer
CERT02          430.0000      10448045      Brenda Norton
CINC01      248.2200      10448038      Beatrice Valdez

RAW DATA
=======
CLT_ID     CUR_BAL         Debtor_ID       Name1          
CERT02          525.0000      10448038      Beatrice Valdez
CERT02          330.1600      10448039      Beth Kerr
CERT02          612.8100      10448040      Beverly Lagler
CERT02          530.0000      10448041      Bobby Sockwell
CERT02          230.0000      10448041      Bobby Sockwell
CERT02          530.0000      10448041      Bobby Sockwell
CERT02          530.0000      10448041      Bobby Sockwell
CERT02          330.0000      10448041      Bobby Sockwell
CERT02          530.0000      10448041      Bobby Sockwell
CERT02          530.0000      10448041      Bobby Sockwell
CERT02          530.0000      10448041      Bobby Sockwell
CERT02          530.0000      10448041      Bobby Sockwell
CERT02          530.0000      10448041      Bobby Sockwell
CERT02          230.0000      10448042      Brandy Brooks
CERT02          248.3900      10448043      Brandy Gibson
CERT02          550.9100      10448044      Brenda Brewer
CERT02          430.0000      10448045      Brenda Norton
CINC01      224.0000      10448038      Beatrice Valdez
CINC01      024.2200      10448038      Beatrice Valdez

Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Is this is correct?

SELECT CLT_ID, Sum(CUR_BAL) AS SumOfCUR_BAL, List_Date, Debtor_ID, Name1, Name2, WorkList, Next_Contact, Updated,
OldWorkList
INTO  #tbl_DistList_DEP_Duplicate
FROM tbl_DistList_DEP GROUP BY CLT_ID, Debtor_ID, Name1;
GO
DELETE FROM tbl_DistList_DEP
GO
INSERT INTO tbl_DistList_DEP
SELECT * from #tbl_DistList_DEP
GO

Thanks.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Ooh, I think you gave me too many points ... sorry, I should have mentioned earlier not to do overdo mine :-) .
Thanks Scott, but it's no big deal. I'm not in this to get into the millions...
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.