how to delete dup records from a table to ?
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
Software/Hardware used:
Software/Hardware used:
ASKED:
October 17, 2008 3:59 PM
UPDATED:
October 20, 2008 4:14 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
