Teradata Forums / Teradata / Training / how to delete duplicate records / Latest Posts

RE: how to delete duplicate records

Nagendhran — Tue, 19 Dec 2006 05:30:27 GMT

Thanks barry,ramakrishna for your suggestions.It really helped me a lot.
rgds.,
Nagendhran

RE: how to delete duplicate records

ramakrishna_vedantam — Tue, 19 Dec 2006 00:26:23 GMT

Export the records to a flatfile and then load using fastload. Fastload does not load the duplicate records.

RE: how to delete duplicate records

Barry — Fri, 15 Dec 2006 08:36:07 GMT

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.

RE: how to delete duplicate records

Barry — Fri, 15 Dec 2006 08:29:55 GMT

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.

how to delete duplicate records

Nagendhran — Fri, 15 Dec 2006 06:27:26 GMT

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