How to simply remove duplicate data from sqlite table

[Vincent Borghi]

Hi, just simple advice needed:

I have a web2py application whose data is made of an sqlite table
(that I have obtained by importing in web2py an excel-generated CSV file).

Now I see that in fact the original CSV data included many duplicate lines,
so my sqlite table has the same duplicate lines that differ only by their id.

What simple method do you recommend to eliminate the duplicate lines
perhaps from within the web2py framework and without stopping the application...?

thanks

[Thadeus Burgess]

You will need to iterate through all of the records, for each record
you need to determine if its data is the exact same anywhere else in
the database. If so, tag it for deletion. At the end you will need to
determine which of the duplicated records to keep, and then delete the
rest.

Does this help?

-Thadeus

> --
>
> You received this message because you are subscribed to the Google Groups
> "web2py-users" group.
> To post to this group, send email to web...@googlegroups.com.
> To unsubscribe from this group, send email to
> web2py+un...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/web2py?hl=en.
>

[Julio]

This looks to be more of a sqlite question than web2py's, however on
MySQL, dupes can be removed with something like this:

Bad Table:
INSERT INTO dupe_table(message) VALUES
('Hello World'),
('Hello World'),
('NCC1701-D'),
('NCC1701-E'),
('NX-01'),
('Hello World')

-- Add this:
alter ignore table dupe_table add unique index `dupetest_index`
(message);
-- After this is done, just remove the index and you'll set:
alter table dupe_table drop index `dupetest_index`;

Dupes are gone. - I don't know if sqllite allows something like that