4

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.

2 Answers 2

16

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. =)

1

SQLite has had window functions since 3.25.0 which are extremely cool, a bit tricky to grasp, and useful for this task.

You can do something like:

delete from sms
where rowid in (
  select rowid
  from (
    select
      rowid,
      row_number() over (
        partition by address, body
        -- order by some_expression
        ) as n
    from sms
  )
  where n > 1
);

Less compact but potentially more powerful than normal group by.

You could factor out the innermost select into a view if you wanted to check its behaviour before running delete!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.