issue with removing duplicates from a database without removing the original

Question

Ive been stuck on this all day and finally found a script which would remove all data with the exception of the original, anyway, i done the necessary changes to allow it to work with my code, unfortunately it is spitting out an syntax error. Ive been looking at it for about 2 hours now and i just cant see it. Anyway, im using the MySQL DB and heres the script im using..

DELETE * 
FROM music u
JOIN (

SELECT melody, chorus1, chorus2, MIN( id ) AS minid
FROM music
GROUP BY melody, chorus1, chorus2
HAVING (
COUNT( * ) >1
)
) AS dupusers ON u.melody = dupusers.melody
AND u.chorus1 = dupusers.chorus1
AND u.chorus2 = dupusers.chorus2
ORDER BY u.melody, u.chorus1, u.chorus2
WHERE u.id <> minid

and I get the following error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM music u JOIN ( SELECT melody, chorus1, chorus2, MIN( id ) AS minid ' at line 1

which is..

* 
FROM music u
JOIN (

SELECT melody, chorus1, chorus2, MIN( id ) AS minid

`DELETE * FROM table` is not valid SQL. `DELETE FROM table` would be. — Romain, Aug 01 '12 at 10:40

score 1 · Accepted Answer · edited May 23 '17 at 12:03

its because you have syntax error at DELETE * and you have used WHERE clause after ORDER BY clause. no need to use ORDER BY clause in DELETE query without limit try:

DELETE u
FROM music u
    INNER JOIN (
        SELECT melody, chorus1, chorus2, MIN( id ) AS minid
        FROM music
        GROUP BY melody, chorus1, chorus2
        HAVING COUNT(*) >1
    ) dupusers
    ON u.melody = dupusers.melody
        AND u.chorus1 = dupusers.chorus1
        AND u.chorus2 = dupusers.chorus2
WHERE u.id <> minid;

another easy way to remove duplicates is to add unique index on table see here.

ALTER IGNORE TABLE music ADD UNIQUE KEY ix1(melody, chorus1, chorus2);

issue with removing duplicates from a database without removing the original

1 Answers1