Need to delete duplicate records from table

Database

Need to delete duplicate records from table

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
5 REPLIES

Re: Need to delete duplicate records from table

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
Teradata Employee

Re: Need to delete duplicate records from table

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

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.
rgs
N/A

Re: Need to delete duplicate records from table


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

I have created my own way to delete duplicate record. below is the example:

1. TABLE DDL.

--drop table employee;

create multiset table employee

(

no_seq int,

name char(5),

age int,

dob date format 'YYYY-MM--DD'

)

primary index(no_seq)

;

--2. TABLE DATA INSERT.

insert into employee values(1,'mohit',26,'1989-04-09');

insert into employee values(1,'mohit',26,'1989-04-09');

insert into employee values(1,'mohit',26,'1989-04-09');

insert into employee values(2,'rohit',26,'1989-04-09');

insert into employee values(2,'rohit',26,'1989-04-09');

--3. APPROCH

--3.1 add dummy flag column.

alter table employee

add dummy int;

--3.2 creating required data using rank function.

insert into employee

sel no_seq,name,age,dob,ROW_NUMBER() OVER (partition by no_seq order by dob) rank1

from employee

qualify rank1=1

--3.3 deleting unwanted duplicate data.

del from employee

where dummy is null;

--3.4 alter for back to orignal table DDL.

alter table employee

drop dummy int;