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
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:
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.
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.
Contact Us