CODEWITHSUNDEEP
androidsqlitedaoandroid-room
Tanasis
Tanasis

Reputation: 826

Room: DAO query that removes duplicates given multiple columns as criteria

Assuming we are using Room and have a DAO which implements our interactions with an SQLite table. Is there a way to construct a query that can identify one or more columns that have the same value, i.e. considered duplicates, and remove them?

E.g. we have this table:

primKey | name | lastname | phone |
 1        foo      bar       222
 2        foo      bar       333  
 3        oof      rab       123

In this case, we consider name and lastname to be the criteria to determine if the row is identical. So if name and lastname is the same we want to keep only one row, as there exist duplicates. Here row 1, 2 are duplicate.

More or less this but I cannot use for example USING table, I am getting errors. And I am also not sure how to use multiple columns as criteria.

Upvotes: 2

Views: 2591

Answers (2)

dglozano
dglozano

Reputation: 6607

Try creating the following Query in your DAO interface:

@Dao
public interface MyDaoTest {

    ...

    @Query("DELETE FROM test
            WHERE id NOT IN (SELECT MIN(id) FROM test GROUP BY name, lastName)")
    void deleteDuplicates();

    ...
}

That query, will delete all the rows in which the name and lastName are duplicated.

For example, if we have the following table, we can see that the rows with id 1, 2 and 6 are duplicates, the same with the rows with id 3 and 4, whereas the row with 5 has no duplicates.

original table with duplicates

This query will group the rows by name and lastName, leaving only the one with the minimum ID. By doing that, we make sure we get only one row per every set of duplicate rows (the one with the minimum ID)

SELECT MIN(id)
FROM test
GROUP BY name, lastName;

result of select min

Finally, if we delete all the the rows whose IDs are not contained in the result of that internal query, we will get rid of the duplicates.

DELETE
FROM test
WHERE id NOT IN
(SELECT MIN(id)
FROM test
GROUP BY name, lastName);

result

Upvotes: 3

musooff
musooff

Reputation: 6882

You can use Composite Primary Key

@Entity(primaryKeys = ["name", "lastname"])
data class User(
    // you can ignore your primaryKey field
    val name: String,
    val lastname: String,
    val phone: Long
)

So whenever you insert a new value you can override or just ignore if name and lastname are the same.

@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertReplace(user: User)

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insertIgnore(user: User)

Upvotes: 1

Related Questions