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
i want to delete the duplicate rows in s table based on few columns, with out using temp table
Moderator: NorbertKrupa
-
- Newbie
- Posts: 1
- Joined: Thu Aug 31, 2017 12:57 pm
- JimKnicely
- 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
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)
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

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

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