On Wed, Apr 16, 2014 at 9:46 AM, Christoph P.U. Kukulies <k...@kukulies.org>wrote:
> Maybe been asked a hundred times but Im seeking for an elegant way to get > rid of duplicate rows which had been entered during development. > I have a database "versionen.sq3" having a table created by > > CREATE TABLE version (major TEXT, minor TEST, date DATE) > > Due to running across an iPython notebook several times it happened that I > now have every row occuring as duplicate around five times or so. > > Two questions: > > 1. would like to delete all duplicate rows by some SQL statement > DELETE FROM version WHERE rowid NOT IN (SELECT min(rowid) FROM version GROUP BY major, minor, date); > > 2. defend myself against this happening again, that is, major,minor and > date may only occur one time in ther respective combination. > CREATE UNIQUE INDEX version_idx1 ON version(major,minor,date); > > I think I'll have to create some UNIQUE Key. Also, when INSERTing I would > like to avoid error messages being thrown in case of a duplicate row coming > along. > > Thanks. > > -- > Christoph > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
