How do you delete duplicate rows in MySQL?

To remove duplicate lines, you can follow these steps:

1. Identify duplicate rows: Use a SELECT statement with a GROUP BY clause to identify duplicate rows. For example, suppose there is a table named table1 with columns name and age, you can use the following query to find duplicate combinations of name and age:

SELECT name, age, COUNT(*) FROM table1 GROUP BY name, age HAVING COUNT(*) > 1;

1. Create a temporary table using the CREATE TABLE statement to store the duplicate rows that need to be deleted. For example, let’s say we create a temporary table called temp_table with the same name and age columns.

CREATE TABLE temp_table AS SELECT name, age FROM table1 GROUP BY name, age HAVING COUNT(*) > 1;

1. Remove duplicate rows: Use the DELETE statement and INNER JOIN clause to delete duplicate rows. By performing an INNER JOIN operation between a temporary table and the original table, the duplicate rows to be deleted can be identified.

DELETE table1 FROM table1 INNER JOIN temp_table ON table1.name = temp_table.name AND table1.age = temp_table.age;

1. Drop temporary table: Removing a temporary table to free up memory space.

DROP TABLE temp_table;

By following the above steps, you can remove duplicate rows from a MySQL table. Before executing the deletion operation, it is advisable to backup the data to prevent accidental deletion.