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