List:General Discussion« Previous MessageNext Message »
From:Aveek Misra Date:May 9 2011 2:43pm
Subject:Re: Deleting the duplicate values in a column
View as plain text  
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=1
> 

Thread
Deleting the duplicate values in a columnabhishek jain9 May
  • Re: Deleting the duplicate values in a columnAveek Misra9 May
    • Re: Deleting the duplicate values in a columnAveek Misra9 May