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:

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:

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:

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:

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.

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.