Deleting duplicate records

[Shruthi]

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?

[JimKnicely]

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)