Re: delete duplicate rows, how???
select distinct * from dupl_table into temp temp_table;
delete from dupl_table where rowid != -1; -- to delete without prompt
;-)
insert into dupl_table select * from temp_table;
This should work in an acceptable manner, provided the table is not
HUGE.
Vijay R. Anisetti
Peter Lancashire wrote:
}
} Ramki Natesan wrote:
} >
} > Hellow friends,
} > Is there a way to delete all the duplicate rows in a given table with
} > simple sql statement in Informix?
} >
} > /Ramki.
} Version 7.1 will do it. Add an index with the set violations table on
} and it will tell you which the problem rows are.
}
} If the table is not too big you could unload it and use the Unix sort
} and uniq commands to remove duplicates.
}
} Relational database theory demands that this task is difficult. It
} should be impossible but for the sneaky rowids.
} --
} Peter Lancashire
} Mail: Peter.Lancashire.PL1 at bayer.co.uk
} Information Systems Specialist, Bayer plc
} Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK
} Tel: +44-1635-562258, Fax: +44-1635-562281