I want to delete duplicates from a Redshift table that are true duplicates. Below is an example of two rows that are true duplicates.

Since it is Redshift, there are no primary keys to the table. Any help is appreciated.

I tried using window functions row_number(), rank(). Neither worked as when applying Delete command, SQL command cannot differentiate both rows.

Trial 1:

The below command deletes both rows

DELETE From test_table
where (id) IN
(
  select \*,row_number() over(partition by id) as rownumber from test*table where row*number !=1
);

Trial 2:

The below command retains both rows.

DELETE From test_table
where (id) IN
(
   select \*,rank() over(partition by id) as rownumber from test*table where row*number !=1
);

CodePudding user response：

All row values are identical. Hence you unable to delete specific rows in that table.

In that I would recommend to create dummy table, and load unique records.

Steps to follow:

1. create table dummy as select * from main_table where 1=2
2. insert into dummy(col1,col2..coln) select distinct col1,col2..coln from main_table;
3. verify dummy table.
4. Alter table main_table rename to main_table_bk
5. alter table dummy rename to main.
6. after complete your testing and verification, drop main_table_bk

Hope it will help.

CodePudding user response：

You cannot delete one without deleting the other as they are identical. The way to do this is to:

1. make a temp table with (one copy) of each duplicate row
2. (within a transaction) delete all rows from the source table that match rows in the temp table
3. Insert temp table rows into source table (commit)