delete multiple entry in mysql but keep one php and mysql

Question

i having a problem in cleaning data from the entry in database.

so i have a table "users" with the field (id, name, email, phone)

the problem i have is to delete the duplication based on the phone number. i have around 30k of data entry and i need to make sure that each of the data in the table must consist a record with different "phone number" bcoz right now what i have is (example: 3 same people with the same phone number

name:   Phone No:
john     1234
john     1234
john     1234

i only need to keep one record with one phone number.

is there any php script than can work on this case faster.hope you guys can help me.

so run a SELECT on all records for the same phone number. If found, DELETE it for the different id; what's so hard about that? — Funk Forty Niner, Feb 23 '18 at 19:11
in any case, you should have set a UNIQUE constraint from the beginning. — Funk Forty Niner, Feb 23 '18 at 19:12
btw; you're going to wait an awful long time for the circus to come to town and hoping the magician's still part of the company (payroll) to get a solution here. — Funk Forty Niner, Feb 23 '18 at 19:16
https://dba.stackexchange.com/questions/21314/remove-duplicate-rows-in-mysql-table-that-does-not-contain-primary-key — Juan Carlos Oropeza, Feb 23 '18 at 19:25
if you have an unique field so you can diference between each name is easy Possible duplicate of [Remove duplicate rows in MySQL](https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) otherwise you need create a temporal table as the previous link. — Juan Carlos Oropeza, Feb 23 '18 at 19:34

score 0 · Answer 1 · answered Feb 23 '18 at 19:32

0

You can delete with a join. In this example you keep the lowest users.id.

DELETE t1 FROM users AS t1
LEFT JOIN
(SELECT MIN(id) AS min_id FROM users GROUP BY phone) AS t2
ON t1.id = t2.min_id WHERE t2.min_id IS NULL;

answered Feb 23 '18 at 19:32

Max Oriola

1,296
12
8

score 0 · Answer 2 · answered Feb 23 '18 at 19:40

0

You can use ALTER TABLE

ALTER IGNORE TABLE users
ADD UNIQUE INDEX p (phone);

This will drop all the duplicate rows and doesn't allow future INSERT with the same phone value.

answered Feb 23 '18 at 19:40

Aso Strife

1,089
3
12
31

delete multiple entry in mysql but keep one php and mysql

2 Answers2