Sorry I jumped the gun, didn't realize it was a delete. The problem is that GROUP BY does not work with DELETE. You can try this:
DELETE FROM <mytable> where id IN (SELECT id from <my table> GROUP BY id HAVING COUNT(*) > 1); Not sure whether this will work though. You can always use a temp table for this purpose although that is not so appealing as doing it in one query CREATE TABLE tmp LIKE <mytable>; INSERT INTO tmp SELECT * from <my table> GROUP BY id HAVING COUNT(*) = 1; DROP TABLE <mytable>; RENAME TABLE tmp TO <mytable>; Thanks Aveek On May 9, 2011, at 7:54 PM, Aveek Misra wrote: > SELECT * from <table> group by id having count = 1; > > On May 9, 2011, at 5:45 PM, abhishek jain wrote: > >> hi, >> If we have a following mysql table: >> Name - ids >> A 1 >> B 1 >> C 2 >> D 3 >> >> I want to remove all duplicate occurances and have a result like >> Name - ids >> C 2 >> D 3 >> >> how can i do that with a query in mysql >> >> Pl. help asap >> -- >> Thanks and kind Regards, >> Abhishek jain > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=ave...@yahoo-inc.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
