Deleting duplicate records

Moderator: NorbertKrupa

Post Reply
Shruthi
Newbie
Newbie
Posts: 1
Joined: Sat Sep 21, 2013 12:07 pm

Deleting duplicate records

Post by Shruthi » Tue Sep 24, 2013 12:13 pm

Hi,
here is my data
id |name |salary |dept
1 |a |1000 | 10
2 |b |2000 | 20
3 |c |3000 | 30
1 |a |1000 | 10
3 |c |3000 |30

in above data i want to delete duplicate records(id 1,id 3)
How can i delete in vertica?

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

Re: deleting duplicate records

Post by JimKnicely » Tue Sep 24, 2013 8:05 pm

Hi,

There have been many related posts. Try searching on the keyword duplicate.

For example:

viewtopic.php?f=48&t=1366
viewtopic.php?f=6&t=1092&p=3491

My recommendation is to add a surrogate key on your tables so that you can guarantee that you'll always have a unique row identifier.

You'll probably have to do something like this where dups in my table like yours:

Code: Select all

dbadmin=> select * from dups;
 id | name | salary | dept 
----+------+--------+------
  1 | a    |   1000 |   10
  2 | b    |   2000 |   20
  3 | c    |   3000 |   30
  1 | a    |   1000 |   10
  3 | c    |   3000 |   30
(5 rows)

dbadmin=> create table dups_new like dups;
CREATE TABLE

dbadmin=> insert into dups_new select distinct * from dups;
 OUTPUT 
--------
      3
(1 row)

dbadmin=> drop table dups;
DROP TABLE

dbadmin=> alter table dups_new rename to dups;
ALTER TABLE

dbadmin=> select * from dups;
 id | name | salary | dept 
----+------+--------+------
  1 | a    |   1000 |   10
  2 | b    |   2000 |   20
  3 | c    |   3000 |   30
(3 rows)
Jim Knicely

Image

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

Post Reply

Return to “New to Vertica Database Development”