query for delete repeated same name

Discussion in 'PHP' started by dineshsingh1984, Sep 17, 2014.

0
  1. #1
    my table is test
    id name
    -------------------------
    1 prince
    2 king
    3 queen
    4 prince
    5 prince
    6 prince

    I want to delete repeated name like prince repeated many times. so i want to remove repeated data and remain only one time every name.

    my query is :
    $sql = mysql_query("delete from test where id in (select id from (SELECT id FROM test GROUP BY name having COUNT(*) > 1)temp");
    but it's not working........
    plz help me................
     
    Last edited: Sep 17, 2014
    dineshsingh1984, Sep 17, 2014 IP
  2. darkvisje

    darkvisje Well-Known Member

    Messages:
    231
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    120
    #2
    ALTER IGNORE TABLE foobar ADDUNIQUE(title, SID)

    For more options see:
    http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-mysql-table
     
    darkvisje, Sep 17, 2014 IP
  3. Vibrantu

    Vibrantu Greenhorn

    Messages:
    11
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    13
    #3
    You can use following query for this delete from test where name in(select name from test group by name having count(*) >1) Hope it helps
     
    Vibrantu, Sep 21, 2014 IP