April 3, 2007 at 12:04 pm
Hi i'm a learner of sql,
i need help in deleting rows from a table that have multiple occurences of a value.
the table has a column named product_id , i need to find out what rows have the product_id value repeated ( i.e not distinct)
how do i do that help please.
April 3, 2007 at 12:40 pm
There are many ways to do this one and I will need to see your table structure to give you the best answer.
Marvin Dillard
Senior Consultant
Claraview Inc
April 3, 2007 at 12:48 pm
Use this query to find which Product_ids are duplicated:
SELECT
DuplicateCount = COUNT(*),
Product_id
FROM TableWithDuplicates
GROUP BY Product_id
HAVING COUNT(*) > 1
Greg
Greg
April 3, 2007 at 12:52 pm
You can run this and take a look at what comes out. If you want to purge everything that has a dupe, change "select product_id" to "Delete". Bear in mind, though, that any product_id caught in the sub-query will be completed cleared from your table. There won't be distinct values of the product_ids left if you run the delete...just not sure what you're after.
select product_id from <table>
where product_id in
(select product_id from <table>
group by product_id
having count(*) > 1)
Ack, Greg beat me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply