How to delete duplicate rows in SQLite?

SQLite is an open-source and serverless database system that does not require any server to perform various queries also it is widely used in the development of embedded software like television and mobile phones Sometimes it might happen that we by mistake insert multiple times similar data into tables which leads to the problem of inconsistency and data integrity. In this article, we will learn about How to delete duplicate rows in SQLite with its syntax examples and so on.

Prerequisites

Before understanding how to perform operations on duplicate row deletion we must have familiarity with SQLite fundamentals and basic SQL commands like SELECT, DELETE and GROUP BY.

Introduction to the Duplicate Rows

Duplicate rows in an SQLite database are defined as multiple records within a table that have the same values in one or more columns. These duplicates can arise due to various reasons, such as data entry errors, inconsistencies in data sources or incomplete data normalization processes. Identifying and managing duplicate rows is important for maintaining data integrity and ensuring efficient database operations. we will learn how to remove duplicate records from the table easily.

Syntax:

DELETE FROM students
WHERE rowid NOT IN (
    SELECT MIN(rowid)
    FROM students
    GROUP BY first_name, last_name
);

Explanation: This SQL query deletes duplicate rows from the “students” table by retaining only the records with the minimum “rowid” for each unique combination of “first_name” and “last_name”. It ensures that only the earliest entry for each distinct student name combination is preserved, effectively eliminating duplicate entries

By following these steps, we’ll effectively declutter our database and ensure that each piece of data is unique, just like ensuring each book on the shelf is distinct. This process not only improves data organization but also enhances the efficiency and performance of our database operations.

Examples of How to Delete Duplicate Rows in SQLite

Example 1: Deleting Duplicate Employee Names

We have a table of employee names and salaries. Some names are repeated.

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    salary REAL
);


INSERT INTO employees (name, salary) VALUES
('John Doe', 50000),
('Jane Smith', 60000),
('John Doe', 50000),
('Michael Johnson', 55000),
('Jane Smith', 60000);

Output:

Employee Table

Let’s delete the duplicate rows form the employees table then check the table.

DELETE FROM employees
WHERE rowid NOT IN (
    SELECT MIN(rowid)
    FROM employees
    GROUP BY name
);

Output:

Employee Output

Explanation:

• We’re identifying duplicate employee names and keeping only the earliest-hired employee with that name.
• The DELETE statement removes the excess copies, leaving us with a neat and organized employee list.

Example 2: Removing Duplicate Student Names

We have a table of student names, last names, and ages. Some names are repeated.

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);


INSERT INTO students (first_name, last_name, age) VALUES
(1, 'John', 'Doe', 25),
(2, 'Jane', 'Smith', 30),
(3, 'John', 'Doe', 25),
(4, 'Michael', 'Johnson', 28),
(5, 'Jane', 'Smith', 30);

Output:

Student Table

Let’s delete the duplicate rows form students the table then check the table.

DELETE FROM students
WHERE rowid NOT IN (
    SELECT MIN(rowid)
    FROM students
    GROUP BY first_name, last_name
);

Output:

Student Output

Explanation:

• We’re identifying duplicate student names and retaining only the records of the oldest students with the same name.
• The DELETE statement removes the redundant student entries, leaving us with a concise and organized student database.

Conclusion

Overall, In this article we have learned about how to delete duplicate rows when table consists multiple duplicates rows to ensure the data integrity and consistency With the help of command such as DELETE and GROUP BY which we learned above in the article will remove redundant entries effectively. By understanding these method allow the developers can fast database maintenance tasks, improve data organization also ensure the accuracy of their applications data.