Mysql – Use “NOT IN” in the same table, Mysql

deleteMySQL

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.

Best Answer

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;
Related Question