Use "NOT IN" in the same table, Mysql

Question

I have a table like that:

table1
    +----+------+--------------+
    | id | name | phone_number |
    +----+------+--------------+
    | 1  |  A   | 111111111111 |
    +----+------+--------------+
    | 2  |  B   | 222222222222 |
    +----+------+--------------+
    | 3  |  C   | 222222222222 |
    +----+------+--------------+
    | 4  |  D   | 111111111111 |                                        
    +----+------+--------------+
    | 5  |  E   | 222222222222 |
    +----+------+--------------+

I want to keep only 1 of the same phone number, don't care name. So, the table should be:

table1
    +----+------+--------------+
    | id | name | phone_number |
    +----+------+--------------+
    | 1  |  A   | 111111111111 |
    +----+------+--------------+
    | 2  |  C   | 222222222222 |
    +----+------+--------------+

I tried many ways, many hrs to delete or select but doesn't work, I did use code to resolve this, but I will appreciate for any explanation, thank you.

DELETE FROM table1 
where id NOT IN (SELECT id FROM table1 group by phone)

always said: You can't specify target table 'table1' for update in FROM clause

SELECT * 
FROM table1 
where id NOT IN (SELECT id FROM table1 group by phone)

always return an empty result.

Answer 1

Given this sample data:

root@localhost:playground > select * from t;
+------+------+--------------+
| id   | name | phone_number |
+------+------+--------------+
|    1 | A    | 111111111111 |
|    2 | B    | 222222222222 |
|    3 | C    | 222222222222 |
|    4 | D    | 111111111111 |
|    5 | E    | 222222222222 |
+------+------+--------------+
5 rows in set (0.00 sec)

first you determine which rows you want to keep.

root@localhost:playground > select min(id), min(name), phone_number from t group by phone_number;
+---------+-----------+--------------+
| min(id) | min(name) | phone_number |
+---------+-----------+--------------+
|       1 | A         | 111111111111 |
|       2 | B         | 222222222222 |
+---------+-----------+--------------+

With this query you can check which rows will be deleted:

root@localhost:playground > select * from t left join (select min(id) id, min(name) name, phone_number from t group by phone_number) a on a.id = t.id and a.name = t.name and a.phone_number = t.phone_number;
+------+------+--------------+------+------+--------------+
| id   | name | phone_number | id   | name | phone_number |
+------+------+--------------+------+------+--------------+
|    1 | A    | 111111111111 |    1 | A    | 111111111111 |
|    2 | B    | 222222222222 |    2 | B    | 222222222222 |
|    3 | C    | 222222222222 | NULL | NULL | NULL         |
|    4 | D    | 111111111111 | NULL | NULL | NULL         |
|    5 | E    | 222222222222 | NULL | NULL | NULL         |
+------+------+--------------+------+------+--------------+
5 rows in set (0.00 sec)

The delete query:

root@localhost:playground > delete t.* from t left join (select min(id) id, min(name) name, phone_number from t group by phone_number) a on a.id = t.id and a.name = t.name and a.phone_number = t.phone_number where a.phone_number is null;
Query OK, 3 rows affected (0.00 sec)

root@localhost:playground > select * from t;
+------+------+--------------+
| id   | name | phone_number |
+------+------+--------------+
|    1 | A    | 111111111111 |
|    2 | B    | 222222222222 |
+------+------+--------------+
2 rows in set (0.00 sec)

Another feasible approach would be this:

CREATE TABLE t_tmp LIKE t;

INSERT INTO t_tmp 
SELECT MIN(id), MIN(name), phone_number
FROM t
GROUP BY phone_number;

RENAME TABLE t TO t_backup, t_tmp TO t;

Answer 2

Another way, using group by, if you don't care about other fields' values:

CREATE TABLE t1 LIKE t;
INSERT INTO t1 SELECT * FROM t GROUP BY phone_number;
RENAME TABLE t TO t_old, t1 TO t;
DROP TABLE t_old;

If the table t is huge, you may want to create it without indexes and after populating the data, you create the indexes:

CREATE TABLE t1 AS SELECT * FROM t GROUP BY phone_number;
ALTER TABLE t1 ADD INDEX ...
RENAME TABLE t TO t_old, t1 TO t;
DROP TABLE t_old;

Answer 3

For the record, there is very important reason why

DELETE FROM table1 
where id NOT IN (SELECT id FROM table1 group by phone);

does not work.

MySQL processes subqueries in such a way that rows can sometimes disappear during its optimization and execution. This is not harmful to SELECT queries using subqueries in its WHERE clause. This does affect DELETE and UPDATE queries with such a WHERE clause. In that instance, it is much better to decompose the query into separate steps.

I wrote about this over the years

• Feb 22, 2011 : Problem with MySQL subquery
• Jan 23, 2013 : Problem with nested UPDATE queries
• Jun 04, 2013 : Delete from table where multiple fields match select subquery from other table
• Jul 11, 2014 : Slow DELETE in MySQL with a subquery