Re: Duplicate Row Removal

Dean Gibson (DB Administrator) wrote:

> CREATE TABLE new_name AS SELECT DISTINCT * FROM old_name;
>
> DROP TABLE old_name;
>
> ALTER TABLE new_name RENAME TO old_name;

The problem with this technique is that it doesn't account for indexes,
foreign key references, and other dependencies.

Another approach is to temporarily add an integer column, populate it
with sequential values, and then use that new column to uniquely
identify the rows that are otherwise duplicates. Then you can use
aggregation to identify and delete the rows you don't need, followed by
dropping the temporary extra column. HTH.

-- BMT

>
> On 2005-11-04 17:15, Peter Atkins wrote:
>
>> All,
>>
>> I have a duplicate row problem and to make matters worse some tables
>> don't have a PK or any unique identifier.
>> Anyone have any thoughts on how to remove dups?
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>