Am 16.04.2014 15:57, schrieb Richard Hipp:
On Wed, Apr 16, 2014 at 9:46 AM, Christoph P.U. Kukulies <[email protected]>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 statementDELETE FROM version WHERE rowid NOT IN (SELECT min(rowid) FROM version GROUP BY major, minor, date);
OK, fine. Worked perfectly.
Though this seems to work, could I achieve this also by a table constraint, like UNIQUE(major,minor,date) ? Can I apply that a posteriori to the table? I tried SQLite database browser 2.0.b1 but can't figure out how.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.
-- Christoph _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
