Craig Smith <cr...@macscripter.net> wrote: > By searching the archives of this list, I was able to come up with > this syntax to identify duplicate records and place a single copy of > each duplicate into another table: > > CREATE TABLE dup_killer (member_id INTEGER, date DATE); INSERT INTO > dup_killer (member_id, date) SELECT * FROM talks GROUP BY member_id, > date HAVING count(*)>1; > > But, now that I have the copies in the dup_killer table, I have not > been able to discover an efficient way to go back to the original > table (talks) and delete them.
You don't need a separate table at all. Try this: delete from talks where exists (select 1 from talks t2 where talks.member_id = t2.member_id and talks.date = t2.date and talks.rowid > t2.rowid); -- or delete from talks where rowid in (select t2.rowid from talks t2 where talks.member_id = t2.member_id and talks.date = t2.date order by t2.rowid offset 1); Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
