RE: delete 2 of the 3 triplicates from a table

From: Powell, Mark <mark.powell2_at_hp.com>
Date: Fri, 7 Feb 2014 18:54:55 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD1187CCB46_at_G6W2491.americas.hpqcorp.net>

Others have already posted on how to find and delete duplicates but I want to ask a couple of questions.

Are you sure the ID in question is supposed to be unique to this table? That is, the ID is not actually a key for a value in another table and the date represents the date when some associated event took place and this event can repeat?

Have you checked to see what if any indexes appear on this table and if so on what columns?

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lyall Barbour Sent: Friday, February 07, 2014 11:31 AM To: oracle-l digest users
Subject: delete 2 of the 3 triplicates from a table

Hi,

  I need to delete 2 of the 3 triplicates i found in a table. Anyone have an easy way to do that?

Oracle 10.2.0.2 table has two columns, the ID that has triplicates in it and a date column (sysdate of when info went into table)

I found the triplicates with

select <id column> from <table>

group by <id column>

having (count(<id column) > 1); -- then sub sequently, > 2 (> 3 returned no rows)

Thanks

Lyall Barbour

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 07 2014 - 19:54:55 CET