|
|
|
Junior Member
 
Group: Forum Members
Last Login: 6/18/2007 5:02:00 PM
Posts: 18,
Visits: 1
|
|
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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 6/30/2008 3:00:56 AM
Posts: 22,
Visits: 5
|
|
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
-:-
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: Yesterday @ 3:21:12 AM
Posts: 280,
Visits: 310
|
|
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.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/25/2008 12:53:07 PM
Posts: 118,
Visits: 60
|
|
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.
Regards
Ramakrishna_Vedantam
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 6/25/2008 7:48:49 PM
Posts: 60,
Visits: 67
|
|
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.
|
|
|
|