Deleting the duplicate values in a column

Deleting the duplicate values in a column

am 09.05.2011 14:15:01 von abhishek.netjain

--000e0ce0b492f02eae04a2d6c94a
Content-Type: text/plain; charset=ISO-8859-1

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

--000e0ce0b492f02eae04a2d6c94a--

Re: Deleting the duplicate values in a column

am 09.05.2011 16:24:45 von Aveek Misra

SELECT * from

group by id having count =3D 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
>=20
> I want to remove all duplicate occurances and have a result like
> Name - ids
> C 2
> D 3
>=20
> how can i do that with a query in mysql
>=20
> Pl. help asap
> --=20
> 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Deleting the duplicate values in a column

am 09.05.2011 16:43:51 von Aveek Misra

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 where id IN (SELECT id from GROUP BY id HA=
VING 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 ;
INSERT INTO tmp SELECT * from GROUP BY id HAVING COUNT(*) =3D 1;
DROP TABLE ;
RENAME TABLE tmp TO ;


Thanks
Aveek

On May 9, 2011, at 7:54 PM, Aveek Misra wrote:

> SELECT * from

group by id having count =3D 1;
>=20
> On May 9, 2011, at 5:45 PM, abhishek jain wrote:
>=20
>> hi,
>> If we have a following mysql table:
>> Name - ids
>> A 1
>> B 1
>> C 2
>> D 3
>>=20
>> I want to remove all duplicate occurances and have a result like
>> Name - ids
>> C 2
>> D 3
>>=20
>> how can i do that with a query in mysql
>>=20
>> Pl. help asap
>> --=20
>> Thanks and kind Regards,
>> Abhishek jain
>=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Daveekm@yahoo-inc.=
com
>=20


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg