Teradata Forums / Teradata / Teradata Database / Need to delete duplicate records from table / Latest Posts

RE: Need to delete duplicate records from table

rgs — Wed, 21 Feb 2007 19:50:29 GMT

You can use an insert select statement:

insert into t1 select distinct * from t2;

where t1 is a set table and t2 is the multiset table with the duplicate rows you want to eliminate. Both tables have the same column definitions.

RE: Need to delete duplicate records from table

ramakrishna_vedantam — Wed, 21 Feb 2007 02:23:35 GMT

Use fastexport 1st to select the rows and then create a flat file of records from the table. And then use fastload to load the records back into the table. Fastload does not load the duplicate records. Your task of eliminating the duplicate rows will be achieved.

RE: Need to delete duplicate records from table

Fred Pluebell — Wed, 21 Feb 2007 00:01:54 GMT

Applications cannot reference internal ROWID directly in current releases. (In some releases it may be possible to enable this deprecated functionality for backward compatibility, but since the option will presumably be removed in a future release you should not design around it.)

Your supported options are: INSERT into another table (either using GROUP BY or Teradata's SET table functionality to remove duplicates), or do a multi-step process: Identify and "save" one copy of each set of duplicates (e.g. INSERT SELECT with GROUP BY ... HAVING COUNT(*) > 1), DELETE all copies of the duplicate rows, re-INSERT the saved single copies.

RE: Need to delete duplicate records from table

Stration — Tue, 20 Feb 2007 21:23:37 GMT

Hi Laskar

Here is the SQL to delete duplicate records,

DELETE FROM TABLE_NAME A
WHERE ROWID <>
(
SELECT MAX(ROWID) or MIN(ROWID) FROM TABLE_NAME B WHERE A.COLUMN_NAME = B.COLUMN_NAME
);

Stration

Need to delete duplicate records from table

rlaskar — Tue, 20 Feb 2007 14:34:22 GMT

Hi,

I have many duplicate records in a table. Need to remove or delete these duplicate records from table. So that only one unique record exists.

Thanks,
rlaskar