Tags
,
Asked 30+ days ago
20 Dec 2016
Views 103
jaman

jaman posted

how to compare data for same table in same database in MySql

Suppose i have table with some structure like this
IdName
1JAMAN
2khuman
3jaman



i want to remove all record with same name but in differ case . suppose above table have two record with same name "jaman"(lowercase) and "JAMAN"(uppercase) , want to remove latest one so only one record for identical name.

i want to know if possible to compare two record form same table in Mysql
Mitul Dabhi

Mitul Dabhi
answered Jan 23 '18 20:16

you can use PHP for it .
1. get the all data which have common value by query
2. delete them one by one through foreach loop

QUERY TO DETECT COMMON VALUE FOR SAME TABLE IN SAME DATABASE
suppose table name is "users" and we are finding list of entries which have common "name"

SELECT a.name, a.id AS idmain, b.id AS idsecond
FROM `users` AS a, `users` AS b
WHERE a.name = b.name
AND a.id <> b.id
AND a.id < b.id
order by a.id

we do join for "users" with "users" it means we are joining with same table.
by joining same table we able to compare same table value with other record.
here we put a.id<>b.id to skip same record to compare with same record . suppose 1-JAMAN should not be compare with 1-JAMAN , it should be compare with 3-jaman
and a.id<b.id means it will remove join duplicatate entry .

you can get all data with same value as like below

idnameidname
1JAMAN3jaman


you can use above query with php and get all list and delete by b.id so keep it old one intact.

suppose if you remove a.id<b.id from query

SELECT a.name, a.id AS idmain, b.id AS idsecond
FROM `users` AS a, `users` AS b
WHERE a.name = b.name
AND a.id <> b.id
 
order by a.id


you will get result
idnameidname
1JAMAN3jaman
3jaman1JAMAN


which is undesirable.
Edit Answer
Share
its good but i need one query which find the row which have common value and also delete latest one from two or more . it should be done in one query . not like one for select and than again second delete query for deleting it.sorry i am thinking about the performance also - jaman  
Dec 20 '16 06:29
Post Answer