mysql delete duplicate rows but keep one – How to delete duplicates on a MySQL table?

mysql delete duplicate rows but keep one or mysql delete duplicate rows but keep first, mysql remove duplicate rows based on two columns, mysql remove duplicate rows from join and duplicate rows in mysql.

mysql delete duplicate rows but keep one

MySQL delete duplicate records but keep latest db2 sql delete duplicate rows but keep one, mysql select duplicate rows but keep one and postgresql delete duplicate rows but keep one.

Example – How to delete duplicates on a MySQL table?

Suppose you have a table member, with the following columns:

member (m_profile_name, last_name, start_date)

In order to delete the rows with a duplicate m_profile_name column:

delete
from member using member,
    member m1
where member.id > m1.id
    and member.m_profile_name = m1.m_profile_name  

  select id, mcode
    from tamilrokers;

ID                     mcode                
---------------------- -------------------- 
1                      tamil                  
2                      hindi                  
3                      english                  
4                      hindi                  
5                      bolyy                  
6                      south                  
7                      tamil                  
8                      tamil                  
9                      south 

So, we need to find all repeated mcodes and delete all of them, but the latest id.
In this case, tamil, hindi and south are repeated, so we want to delete IDs 1, 7, 2 and 6.

      select mcode 
        from tamilrokers
       group by mcode
      having count(*) > 1;

mcode                
-------------------- 
tamil                  
hindi                  
south  

Finally we can now delete all of these mcodes with an Id smaller than LASTID.

delete tamilrokers
  from tamilrokers
 inner join (
  select max(id) as lastId, mcode
    from tamilrokers
   where mcode in (
              select mcode 
                from tamilrokers
               group by mcode
              having count(*) > 1
       )
   group by mcode
) duplic on duplic.mcode = tamilrokers.mcode
 where tamilrokers.id < duplic.lastId;

mysql delete duplicate rows but keep one

mysql remove duplicates

DELETE FROM members WHERE id 
       NOT IN ( SELECT id FROM members 
                   GROUP BY field_1, field_2)

Don’t miss : Mysql Delete Duplicate Rows

mysql delete duplicate rows but keep one

DELETE m1 FROM Messages m1
INNER JOIN Messages m2 
WHERE
    m1.id > m2.id AND 
    m1.mcode = m2.mcode;

mysql delete duplicates

DELETE FROM Messages
WHERE ID NOT IN
      (SELECT *
       FROM (SELECT max(ID)		
             FROM Messages
             GROUP BY mcode) t);
 -- ⇓ run it ⇓ (Fiddle source link)

how to delete all duplicate items in mysql

DELETE FROM teamsInfo WHERE fID 
       NOT IN ( SELECT fID FROM teamsInfo 
                   GROUP BY memberID, FriendsmemberID, IsSpecial, CreatedBy)

mysql delete duplicate rows except one

DELETE FROM NAMES
 WHERE id NOT IN (SELECT * 
                    FROM (SELECT MIN(n.id)
                            FROM NAMES n
                        GROUP BY n.name) x)

I hope you get an idea about mysql delete duplicate rows but keep one.
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.