Question 252192 in Mysql Question posted in
The official MySQL documentation can be found here.

How to delete duplicate data in mySql on new version?

How to delete duplicate data in mySql on new version?

SchoolId    StudentName 
101        Majid Bhuiyan
101        Majid Bhuiyan
102        Arif
103        Tamim Rahman
104        Sourov Doggesh

I expect it mySql code. How to delete only one row in that duplicate row.

SchoolId: 101 StudentName: Majid Bhuiyan
this is two time present in the data table.
Please, Someone help me to remove this problem.

Tags:

2

Answers


  1. 0

    You can find similar rows by group by and aggregate id by min or max depending on what record you want to delete.

    DELETE FROM DETAILS
WHERE id NOT IN (
    SELECT MIN(id)
    FROM DETAILS
    GROUP BY EMPNAME, DEPT, CONTACTNO, CITY
);
Select * FROM DETAILS;

    As you can see here am deleting the duplicate record with min id (meaning the one created first, if the ids are auto-generated).

    Hope this helps !

    Login or Signup to reply.
  2. 0

    In your case you can delete the duplicate rows by using join but I will suggest you to go with a simple method that is to create a new table using the below code

    CREATE TABLE copy_of_table 
SELECT DISTINCT SchoolId,StudentName
FROM table_name;

    It will create a new table and will take only the unique value of SchoolId from your table and hence the duplicate rows will not be there in the new table

    Upvote if you like the answer

    While creating a table you must set a column as UNIQUE or PRIMARY key(Most of the time it is the Id column) to avoid such things

    The syntax for this is

    CREATE TABLE Persons (
    ID int NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
);

    CREATE TABLE Persons (
    ID int NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search