SQLite – How to Remove Duplicates

duplicationsqlite

Given the following table

CREATE TABLE `sms` (
    `_id`   INTEGER,
    `thread_id` INTEGER,
    `address`   TEXT,
    `person`    INTEGER,
    `date`  INTEGER,
    `protocol`  INTEGER,
    `read`  INTEGER DEFAULT 0,
    `status`    INTEGER DEFAULT -1,
    `type`  INTEGER,
    `reply_path_present`    INTEGER,
    `subject`   TEXT,
    `body`  TEXT,
    `service_center`    TEXT,
    PRIMARY KEY(_id)
);

I'd like to delete all rows where address and body are identical. In other words, eliminate dups based on address and body, leaving only the first occurrence.

Best Answer

If you just want to disambiguate two rows with similar content, you can use the ROWID functionality in SQLite3, which helps uniquely identify each row in the table.

Something like this:

DELETE FROM sms WHERE rowid NOT IN (SELECT min(rowid) FROM sms GROUP BY address, body);

should work to get you the rows with the minimum rowid, which will be the first occurrence of that row in the table.

Be sure to run this in a transaction however, to make sure that it does exactly what you want, and of course, take a backup beforehand. =)

Related Solutions

DB2 – Remove All Records with Duplicates

Assuming your desired uniqueness constraint is (one,two), here is one way to do it:

DELETE FROM session.test WHERE (one, two) IN ( SELECT one, two FROM session.test GROUP BY one, two HAVING COUNT(*) > 1 )

PostgreSQL – How to Remove Duplicates and Keep the Most Informative Record

Your question is a bit light on definitions. Assuming:

You define the "least amount of information" with how many of the relevant columns are NULL.
Primary key is adr_id.
Duplicates are marked with a column dupe_id to indicate groups of duplicates.

Since it's also vague what to do exactly, I create a separate table with the dupe ranking:

CRATE TABLE adr_dupe_rank AS
SELECT adr_id, dupe_id
     , rank() OVER (PARTITION BY dupe_id
                    ORDER BY (nr_of_beds  IS NULL)::int
                           + (nr_of_baths IS NULL)::int
                        -- + (...)::int  -- more terms
                   ) AS rnk
FROM   address;

false translate to 0, true translates to 1. So rows with the fewest empty columns are ranked first. Master rows end up with rnk = 1. Dupes get higher rnk numbers.

The window function rank() assigns 1 to all rows sharing the lowest score per dupe_id. Add enough columns or other terms to break ties and get one winner per dupe_id. Or deal with multiple winners separately.

You can then do as you please. To just delete dupes:

DELETE FROM address a
USING  adr_dupe_rank d
WHERE  a.adr_id = d.adr_id
AND    d.rnk > 1;

Alternatively you can use the above query as derived table in the DELETE directly.

Best Answer

Related Solutions

DB2 – Remove All Records with Duplicates

PostgreSQL – How to Remove Duplicates and Keep the Most Informative Record

Related Question