Deleting Duplicate Rows from SQLite Database

Deleting duplicate rows from sqlite database

You need a way to distinguish the rows. Based on your comment, you could use the special rowid column for that.

To delete duplicates by keeping the lowest rowid per (hash,d):

delete   from YourTable
where    rowid not in
         (
         select  min(rowid)
         from    YourTable
         group by
                 hash
         ,       d
         )

How to remove duplicate rows except one?

You can choose to keep the min or max of rowid grouping by the 3 columns shown.

delete from myTable
where rowid not in (select min(rowid)
                    from myTable
                    group by name,company,position)

How do I delete duplicate rows in sqlite where every column including the ID is dupplicate?

For what is related only at the deletion of the duplicated row reffering to C15 you could find all the min(id) group by C15 .. so this are the single row for each C15 value
and the delete the others eg

  delete from 
  book1
  where id not in ( 
  select min(id)
  from Book1
  group by C15) 

but if you have a full exactly duplicated rows you could do useìin these steps ..

1 ) You can create a temporary table with the distinct result for duplicated eg:

create table my_temp_distinct  as  
select col1, col2 ... 
from Book1
group by col1, col2, ... 
having count(*)> 1

2 ) then delete all the rows with duplicated rows

  delete from 
  book1
  where id in ( 
  select min(id)
  from Book1
  group by C15) 

3 ) and last insert using select form the parked rows

insert into Book1 (col1, col2 ....)
select col1, col2, ...
from my_temp_distinct

Delete duplicate rows in sqlite database

In a GROUP BY clause you must enumerate all the columns.

For your case, I think it's better to create another table table2 with all the distinct rows of table1, then delete all rows of table1 and finally insert all rows from table2 to table1:

CREATE TABLE table2 AS SELECT DISTINCT * FROM table1;
DELETE FROM table1;
INSERT INTO table1 SELECT * FROM table2;
DROP TABLE table2;

See a simplified demo.

Delete Duplicate Rows in Large Sqlite Table

This should be doable in pure SQL:

CREATE TABLE temp_table as SELECT DISTINCT * FROM source_table;
DELETE FROM source_table; 
INSERT INTO source_table SELECT * FROM temp_table

Delete all but one duplicate rows in SQLite for version 3.22.0?

I think sqlite do not support alias with delete.

Try following query:

delete from Data
where exists (select 1 from Data t2
              where data.code = t2.code and data.issue = t2.issue
                and data.id < t2.id);

Sqlite Query to remove duplicates from one column. Removal depends on the second column

Your table has rowid as primary key.

Use it to get the rowids that you don't want to delete:

DELETE FROM comparison
WHERE rowid NOT IN (
  SELECT rowid
  FROM comparison
  GROUP BY STK_CODE
  HAVING (COUNT(*) = 1 OR MIN(CASE WHEN min > 0 THEN min END))
)

This code uses rowid as a bare column and a documented feature of SQLite with which when you use MIN() or MAX() aggregate functions the query returns that row which contains the min or max value.

See a simplified demo.

how can I delete duplicates in SQLite?

SQLite has a special column, ROWID created on every table by default (you can switch it off using the WITHOUT ROWID modifier, but be very sure before doing so).

This means that we can identify specific rows within sets of duplicates, for example, finding the first entry for a value:

SELECT messdatum, MIN(ROWID) FROM lipo

So one way to remove duplicates might be this:

DELETE FROM lipo
WHERE rowid NOT IN (
  SELECT MIN(rowid) 
  FROM lipo 
  GROUP BY messdatum
)

---

Related Topics