Display duplicate entries, mysql query

[acctman]

can someone assist with a query code that I can run in phpmyadmin to display all duplicate entries in  field 'm_user'

[Barand]

SELECT m_user, COUNT(*) as usercount

FROM tablename

GROUP BY m_user

HAVING usercount > 1

[acctman]

SELECT m_user, COUNT(*) as usercount

FROM tablename

GROUP BY m_user

HAVING usercount > 1

 

thanks, instead of GROUP can all duplications be listed so i can select and delete them

[Barand]

try

SELECT t.* FROM tablename t
INNER JOIN 
    (SELECT m_user, COUNT(*) FROM tablename
        GROUP BY m_user HAVING COUNT(*) > 1) as X 
    ON t.m_user = X.m_user

[acctman]

this site forum kicks a$$ a lot of smart people on here.

 

thanks Barand

[acctman]

try

SELECT t.* FROM tablename t
INNER JOIN 
    (SELECT m_user, COUNT(*) FROM tablename
        GROUP BY m_user HAVING COUNT(*) > 1) as X 
    ON t.m_user = X.m_user

 

i'm using the same code on something else and I need to be able to delete the entries. the coding works and displays all the duplicate rows but when i check all and seletct delete...

 

DELETE FROM `rate_members` WHERE `t`.`m_id` = 84198 LIMIT 1;

DELETE FROM `rate_members` WHERE `t`.`m_id` = 61533 LIMIT 1;

DELETE FROM `rate_members` WHERE `t`.`m_id` = 79875 LIMIT 1;

DELETE FROM `rate_members` WHERE `t`.`m_id` = 62804 LIMIT 1;

 

i receive this error

 

SQL query: 

DELETE FROM `rate_members` WHERE `t`.`m_id` =84198 LIMIT 1

MySQL said: 

#1109 - Unknown table 't' in where clause

 

 

[Barand]

Either remove the `t`

 

or assign it as an alias for the table

 

DELETE FROM `rate_members` t WHERE `t`.`m_id` = 62804 LIMIT 1;