On Tue, 6 Jan 2009 08:29:43 -0800, Craig Smith
<cr...@macscripter.net> wrote in General Discussion of
SQLite Database <sqlite-users@sqlite.org>:
>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. My plan was to delete all the records
>from talks that match the criteria of the records now in dup_killer,
>and then INSERT the records from dup_killer back into talks before
>DROPPING dup_killer. At this point, I am stuck. Is there an
>efficient method to do this with SQLite, or should I just use a shell
>script?
If you can identify duplicates using (member_id, idate), the
talks table should have those as its primary key and you
wouldn't have duplicates to begin with.
The most preferred date format, julianday, wich uses
datatype REAL is not suitable here, because it is hard to
compare equality of two reals reliably. I'd use seconds
since epoch.
ALTER TABLE talks RENAME TO talks_with_dups;
CREATE TABLE talks (
member_id INTEGER,
idate INTEGER,
somecol text,
PRIMARY KEY (member_id, idate) ON CONFLICT IGNORE
);
INSERT INTO talks SELECT * FROM talks_with_dups;
>Craig Smith
>cr...@macscripter.net
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
