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

Moderator: NorbertKrupa

Post Reply
sathishkumar
Newbie
Newbie
Posts: 1
Joined: Thu Aug 31, 2017 12:57 pm

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

Post by sathishkumar » Thu Aug 31, 2017 1:11 pm

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

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

Post by JimKnicely » Tue Sep 05, 2017 1:16 pm

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)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “dbVisualizer”