I have a table item having just one column name:-
name ----- toys shirt mobile Shirt speaker Toys .... ....
I am trying to delete duplicate rows except one duplicated record with the help of below query
DELETE FROM ( SELECT `name`,`rownumber` FROM (SELECT GREATEST(0,@num := IF(`name` = @NAME, @num + 1, 1),LEAST(1, LENGTH(@NAME := `name`))) AS rownumber,`name` FROM item ORDER BY `name`) AS result1 ) AS result WHERE rownumber >1
The Inner query (Starts from SELECT name and ends with result1) returns individual auto incremented number for each repeating group but when i run entire query then it does not work.
I would like to know what changes i need to make in the inner query (contained in round braces) to delete duplicate rows.