Delete records which have same column value?

Question

Well i have table which have some records with same address_id, i want to delete them all or (if it's possible) to delete all after first one, for example:

id | person_id | address_id
1       80            3
2       226           3
3       31            5
4       46            9
5       11            9
6       123           9

In this case i want to delete records with id (2, 5,6).

Here is my query, but i get error:

You can't specify target table 'person_address' for update in FROM clause

DELETE FROM person_address WHERE id IN (
SELECT address_id
FROM person_address
WHERE person_address.person_id < 100
GROUP BY address_id
HAVING count(*) > 1
)

Select works fine.

Possible duplicate of [How to delete from select in MySQL?](https://stackoverflow.com/questions/4562787/how-to-delete-from-select-in-mysql) — JNevill, Oct 01 '18 at 15:31
It's usually quicker to create a new table, retaining only the records you want to keep — Strawberry, Oct 01 '18 at 15:32
I don't understand the logic by which `(2, 5, 6)` get deleted? — Tim Biegeleisen, Oct 01 '18 at 15:32
@TimBiegeleisen I want to delete all records with this address_id, without first one. — Sahin Urkin, Oct 01 '18 at 15:33

score 1 · Accepted Answer · answered Oct 01 '18 at 15:40

Your select seems to be incorrect too. You can try something like this -

delete from person_address
 where id not in (select *
                    from (SELECT min(id)
                            FROM person_address
                           WHERE person_address.person_id < 400000
                           GROUP BY address_id) temp)
   and person_id < 400000

score 0 · Answer 2 · answered Oct 01 '18 at 15:41

0

You can delete the duplicates record by making 2 objects of tha same table. like

Delete p1 
from person_address p1, person_address p2
where
p1.person_id < 400000
and p1.address_id = p2.address_id
and p1.id > p2.id;

answered Oct 01 '18 at 15:41

Amit chauhan

540
9
22

score 0 · Answer 3 · answered Oct 01 '18 at 15:43

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,person_id INT NOT NULL
,address_id INT NOT NULL
);

INSERT INTO my_table VALUES
(1    ,   80       ,     3),
(2     ,  226  ,         3),
(3     ,  31   ,         5),
(4     ,  46   ,         9),
(5     ,  11   ,         9),
(6     ,  123  ,         9);

CREATE TABLE my_new_table AS
SELECT x.* FROM my_table x JOIN (SELECT MIN(id) id FROM my_table GROUP BY address_id) y ON y.id = x.id;

SELECT * FROM my_new_table;
+----+-----------+------------+
| id | person_id | address_id |
+----+-----------+------------+
|  1 |        80 |          3 |
|  3 |        31 |          5 |
|  4 |        46 |          9 |
+----+-----------+------------+
3 rows in set (0.01 sec)

ALTER TABLE my_new_table MODIFY id SERIAL PRIMARY KEY;

ALTER TABLE my_new_table MODIFY address_id INT UNIQUE;

Delete records which have same column value?

3 Answers3