How to delete duplicate records in a database (1)

Today, I went to a company in Xi'an for an interview. One of the interview questions was as follows: "How to delete identical records in the data table? If two identical records have different primary keys, then how should we delete it? "At that time, because it was a very simple question, it was directly completed using SQL statements. The interviewer said that there was a problem. I was wrong when I came back to study it. Now

Today, I went to a company in Xi'an for an interview. One of the interview questions was as follows: "How to delete identical records in the data table? If two identical records have different primary keys, then how should we delete it? "At that time, because it was a very simple question, it was directly completed using SQL statements. The interviewer said that there was a problem. I was wrong when I came back to study it. Now

Today, I went to a company in Xi'an for an interview. One of the interview questions was as follows: "How to delete identical records in the data table? If two identical records have different primary keys, then how should we delete it? "At that time, because it was a very simple question, it was directly completed using SQL statements. The interviewer said that there was a problem. I was wrong when I came back to study it. Now I will share the steps of the study.

Study conditions:

Window7 + no-install mysql

1. How to delete records with the same attributes except the primary key

Main Idea: first, identify non-duplicate data records from the data table, save these records to the temporary table, and then delete the original table records to migrate the records in the temporary table.

Procedure:

1. Check that the following table contains two identical records in student, with different records searched by distinct (such as the picture on the right ).

2. Create a temporary table and migrate the data in the figure above to student_temp.

SQL statement: create temporary table if not exists student_temp as (select distinct (name), sex from student );

3. Delete the content of the original table or recreate a new table. In this example, modify the content of student_temp on the basis of the original table and migrate the content to student. The final result is as follows:

SQL used: insert into student (id, name, sex) select null, name, sex from student_temp;

Finally, we can see that the student table has only two different data records.