Delete Duplicate Records

create table temp_dup_mytable as select sid,eid,snum,count(sid) from mytable group by 1,2,3 having count(sid) > 1;

CREATE SEQUENCE temp_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;

ALTER TABLE mytable ADD COLUMN temp_id integer;

UPDATE mytable SET temp_id = nextval('temp_seq') from temp_dup_mytable tpu where mytable.eid = tpu.eid and mytable.sid=tpu.sid and mytable.snum=tpu.snum;

-- debug

select d.* from mytable d where d.temp_id>0;

delete from mytable where temp_id not in (select min(temp_id) from mytable where temp_id > 0 group by sid,eid,snum having count(*) > 0);

-- debug

select d.* from mytable d where d.temp_id>0;

ALTER TABLE mytable drop COLUMN temp_id;

alter table mytable add constraint sid_eid_snum_key unique(sid,eid,snum);