How to delete duplicate data in sql

Delete duplicate data in SQL by using the DISTINCT keyword to remove duplicate values ​​from the query results. Use the DISTINCT keyword to delete duplicate rows from the table together with the DELETE statement.

Delete duplicate data in SQL

The main method of deleting duplicate data in SQL is to use DISTINCT and DELETE statements.

DISTINCT

The DISTINCT keyword is used to remove duplicate values ​​from the query results. It can be used with SELECT statements as follows:

 <code>SELECT DISTINCT column_name1, column_name2 FROM table_name;</code>

This will return a query result where the combination of column_name1 and column_name2 is unique.

DELETE

The DELETE statement is used to delete data from a table. To delete duplicate data, you can use the DISTINCT keyword with the DELETE statement as follows:

 <code>DELETE FROM table_name WHERE (column_name1, column_name2) IN ( SELECT DISTINCT column_name1, column_name2 FROM table_name );</code>

This will delete all duplicate rows in the table that match the result returned by DISTINCT query.

Example

Suppose there is the following table:

 <code>create table my_table ( id int not null auto_increment primary key, name varchar(255) not null, email varchar(255) not null ); insert into my_table (name, email) values ('John Doe', 'john.doe@example.com'); insert into my_table (name, email) values ('John Doe', 'john.doe@example.com'); insert into my_table (name, email) values ('Jane Smith', 'jane.smith@example.com'); insert into my_table (name, email) values ('Jane Smith', 'jane.smith@example.com');</code>

To delete duplicate combinations of name and email in the table:

 <code>DELETE FROM my_table WHERE (name, email) IN ( SELECT DISTINCT name, email FROM my_table );</code>

This will delete the two John Doe lines, leaving the following result:

 <code>select * from my_table; | id | name | email | | --- | --- | --- | | 1 | John Doe | john.doe@example.com | | 3 | Jane Smith | jane.smith@example.com |</code>

The above is the detailed content of How to delete duplicate data in sql. For more information, please follow other related articles on the PHP Chinese website!