1

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.

  • SELECT MIN(id),MIN(name),phone_number FROM t GROUP BY phone_number – Mihai Jul 24 '15 at 4:45
  • are you trying to keep only rows with distinct phone numbers and delete the rest? – Masoud Jul 24 '15 at 6:50
4

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;
  • Why not CREATE TABLE.. AS SELECT... – Mihai Jul 24 '15 at 8:46
  • 1
    @Mihai CREATE TABLE AS creates a table just with same datatypes, nothing else. CREATE TABLE LIKE creates a table including primary key and foreign keys and indexes. – tombom Jul 24 '15 at 8:54
0

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;
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.