how to delete dup records from a table to ?

5 pts.
Tags:
Deduplication
SQL Server database
SQL Server tables
basically i want to remove dups in a table. i have a table called productincludes it has a few fields listed here: ParentProductID ChildProductID SummarizedRowID IncludeStatus LastUpdateDate DisplayOrderByID i ran a update for a business request that made many dup rows and want to clean this up. when i run this select to check on how many dups to verify cleanup: select count(*) ctz,ParentProductID, ChildProductID, IncludeStatus from products_productincludes where IncludeStatus =0 group by ParentProductID, ChildProductID, IncludeStatus order by ctz desc notice i am grouping by only 3 of the rows not all the rows in the table !!! i want only the rows that dup cause of these 3 rows to go away. the rest that are outside the groub by clause should stay. that is to say there can be more dups in the table as long as i dont have any dups on these 3 fields. thats what i want to achieve here. thank you for the help

Answer Wiki

Thanks. We'll let you know when a new response is added.

You need to create a second table that will contain only those rows you want to keep. I would keep only the row with the max(LastUpdateDate)

insert into productincludes_tmp
select distinct * from productincludes a
join productincludes b
on a.ParentProductID = b.ParentProductID
and a.ChildProductID = b.ChildProductID
and a.IncludeStatus = b.IncludeStatus
and a.LastUpdateDate = (select max(last_update_date from productincludes c where c.ParentProductID = b.ParentProductID
and c.ChildProductID = b.ChildProductID
and c.IncludeStatus = b.IncludeStatus);

Then drop the old productincludes and rename productincludes_tmp to productincludes, or truncate productincludes and select productincludes_tmp into productincludes.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following