0
1
Mitul Dabhi
answered Nov 30 '-1 00:00
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
| id | name | id | name | |
|---|---|---|---|---|
| 1 | JAMAN | 3 | jaman |
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
| id | name | id | name | |
|---|---|---|---|---|
| 1 | JAMAN | 3 | jaman | |
| 3 | jaman | 1 | JAMAN |
which is undesirable.
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