Mysql Delete Duplicate Rows

mysql delete duplicate rows : Delete the duplicate rows but keep latest : using GROUP BY and MAX. he go to solution for removing duplicate rows from your result sets is to include the distinct keyword in your select statement.

Contents

One way to remove duplicate rows in a database is to use the MySQL DELETE JOIN statement.

How To Delete Duplicate Rows in MySQL?

Prepare sample data

DROP TABLE IF EXISTS members;

CREATE TABLE members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_nm VARCHAR(50) NOT NULL,
    profilenm VARCHAR(50) NOT NULL, 
    email_address VARCHAR(255) NOT NULL
);

INSERT INTO members (account_nm,profilenm,email_address) 
VALUES ('Kajal ','Sanuman','[email protected]'),
       ('Ramesh','Dave','[email protected]'),
       ('Kamlesh','Faklguni','[email protected]'),
       ('divyesh ','Patel','[email protected]'),
       ('Jagruti ','Bergulfsen','[email protected]'),
       ('divyesh ','Patel','[email protected]'),
       ('dharmik','Hindi','[email protected]'),
       ('Vishal ','tamilrokers','[email protected]'),
       ('Rekha','Khatri','[email protected]'),
       ('Sejal','Dhameliya','[email protected]'),
       ('Depika','Kiria','[email protected]'),
       ('Ramesh','Dave','[email protected]'),
       ('dharmik','Hindi','[email protected]'),
       ('Rekha','Khatri','[email protected]');

This query returns data from the members table:

SELECT * FROM members
ORDER BY email_address;

duplicate email_addresss in the members table:

SELECT 
    email_address, COUNT(email_address)
FROM
    members
GROUP BY 
    email_address
HAVING 
    COUNT(email_address) > 1;

A) Delete duplicate rows using DELETE JOIN statement

DELETE t1 FROM members t1
INNER JOIN members t2 
WHERE 
    t1.id < t2.id AND 
    t1.email_address = t2.email_address;

Delete duplicate rows using an intermediate table

-- step 1
CREATE TABLE members_temp 
LIKE members;

-- step 2
INSERT INTO members_temp
SELECT * 
FROM members 
GROUP BY email_address;


-- step 3
DROP TABLE members;

ALTER TABLE members_temp 
RENAME TO members;

Don't Miss : Delete Duplicate Rows In Sql

Delete duplicate rows using the ROW_NUMBER() function

SELECT id, name, ROW_NUMBER()   
OVER (PARTITION BY name ORDER BY name) AS row_num   
FROM student_members;

DELETE FROM members 
WHERE 
	id IN (
	SELECT 
		id 
	FROM (
		SELECT 
			id,
			ROW_NUMBER() OVER (
				PARTITION BY email_address
				ORDER BY email_address) AS row_num
		FROM 
			members
		
	) t
    WHERE row_num > 1
);

MySQL Delete Duplicate Records

1. Delete Duplicate Record Using Delete Join

DELETE S1 FROM student_members AS S1  
INNER JOIN student_members AS S2   
WHERE S1.id < S2.id AND S1.email_address = S2.email_address;

I hope you get an idea about mysql delete duplicate rows.
I would like to have feedback on my infinityknow.com.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.

mysql delete duplicate rows – 3 ways to Delete Duplicate Rows in MySQL