Page 1 of 1

i want to delete the duplicate rows in s table based on few columns, with out using temp table

Posted: Thu Aug 31, 2017 1:11 pm
by sathishkumar
i want to delete the duplicate rows in s table based on few columns, with out using temp table;

I have a table x and I have column a,b,c,d,e,f,g.

i have no constraints in the table and i want to add constraints to the able for that, i have to delete the duplicate records in the table x.

its working fine if i am using the temp table and for security purpose i should not use this.

please advise

Re: i want to delete the duplicate rows in s table based on few columns, with out using temp table

Posted: Tue Sep 05, 2017 1:16 pm
by JimKnicely
Hi,

Here is one way:

dbadmin=> select * from test;
c1 | c2 | c3 | c4
----+----+----+--------
1 | 1 | 1 | TEST1
1 | 1 | 1 | TEST2
1 | 1 | 2 | TEST3
1 | 1 | 2 | TEST5
1 | 1 | 3 | TEST10
(5 rows)

dbadmin=> delete from test where hash(c1, c2, c3, c4) not in (select hash(c1, c2, c3, c4) from test limit 1 over (partition by c1, c2, c3 order by 1));
OUTPUT
--------
2
(1 row)

dbadmin=> select * from test;
c1 | c2 | c3 | c4
----+----+----+--------
1 | 1 | 1 | TEST1
1 | 1 | 2 | TEST3
1 | 1 | 3 | TEST10
(3 rows)