how to delete duplicate records

Visit Teradata.com

Home

Guidelines

Member List

Welcome Guest ( Login | Register )

This online forum is for user-to-user discussions of Teradata products, and is not an official customer support channel for Teradata. If you require direct assistance, please contact Teradata support.

how to delete duplicate records

Rate Topic

Display Mode

Topic Options

Author

Message

Nagendhran

Posted 12/15/2006 6:27:26 AM

Junior Member

Group: Forum Members
Last Login: 10/19/2007 10:39:17 AM
Posts: 11, Visits: 2

Hi,
Can anyone suggest me on how to delete the duplicate records from a teradata table.I should not create a new similar table,but delete from the existing table only.

thanks,
Nagendhran

Post #6079

Barry

Posted 12/15/2006 8:29:55 AM

Supreme Being

Group: Forum Members
Last Login: 6/9/2008 2:55:55 PM
Posts: 185, Visits: 2

If you're saying you cannot create a new table at all to assist you in removing dups, I don't think there is a way to do that. I would suggest 3 options:

1) Insert/select into a new SET table. Then drop the old table and rename the new one to the old name.

2) Insert/select into a new table grouping by (or using distinct) on all columns. Then drop the old table and rename the new one to the old name.

3) Insert only the dups into a 2nd SET table (by doing a group on all columns with a HAVING COUNT(*) > 1). Then do a delete/join (joining the dups table back to the original table), followed by an insert/select from the dups table into the first (in the same unit of work if you need to). This will at least keep the existing table intact, but the dups will be temporarily deleted before one copy of them is inserted back in.

Post #6082

Barry

Posted 12/15/2006 8:36:07 AM

Supreme Being

Group: Forum Members
Last Login: 6/9/2008 2:55:55 PM
Posts: 185, Visits: 2

Another option that I thought of after my last post is to export the dup rows (with a GROUP BY on all columns and HAVING COUNT(*) > 1) to a file. Then, using Mload or Tpump to bring the rows back in and do a delete and insert for each row.

With this method, there is no need for a 2nd table and you won't see all of the dups rows disappear from the table at the same time. The re-insert of each dup row will occur immediately after the deletion of the rows.

Post #6083