"It can be frustrating to find a way to delete
all of the duplicate rows without deleting the
initial instances.delete from $table_name where rowid in
(
select "rowid" from
(select "rowid", rank_n from
(select rank() over (partition by $primary_key order by rowid) rank_n, rowid as "rowid"
from $table_name
where $primary_key in
(select $primary_key from $table_name
group by $all_columns
having count(*) > 1
)
)
)
where rank_n > 1
)This query selects all of the ‘extra’ rowids
and removes them. It is especially designed for
limiting the query scans to only those records which
have duplicates, which is useful if there’s only a
subset of the table that you are dealing with. If
you want to improve its efficiency for a table with
a high percentage of duplicates, simply remove the
inside where clause."
