Page 1 of 1
Deleting duplicate records
Posted: Tue Sep 24, 2013 12:13 pm
by 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?
Re: deleting duplicate records
Posted: Tue Sep 24, 2013 8:05 pm
by 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)