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

Question

I begined read more in-depth MySql and try solved this task on description. According to description need delete a duplicats from table, but my query not work. In sqllite is work fine. What is the reason?

DELETE
FROM
    Person
WHERE
    Email IN(
    SELECT
        Email
    FROM
        Person
    GROUP BY
        Email
    HAVING
        COUNT(Email) > 1
);

error

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

you didn't specify which duplicate you want to delete of course there is an error — Daniel E., Jun 07 '18 at 09:16
Do you really want to remove both rows if there are duplicates? (Instead of keeping one of them.) — jarlh, Jun 07 '18 at 09:19
Table to be updated, `Person`, is also used to determine which rows to update (actually delete in this case). MySQL doesn't permit this. You can work around it using a nested subquery (the method is explained in the duplicate reference link) — Giorgos Betsos, Jun 07 '18 at 09:22

score 1 · Answer 1 · answered Jun 07 '18 at 09:17

1

You could use a self join to delete duplicates

delete a
from Person a
left join Person b on a.Email = b.Email
                 and a.id > b.id
where b.Email is not null

Demo

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

answered Jun 07 '18 at 09:17

M Khalid Junaid

63,861
10
90
118

Where does it come from? Person a and Person b – Serg Bombermen Jun 07 '18 at 09:22
Its a self join , mean join with itself and aliased as a and b – M Khalid Junaid Jun 07 '18 at 09:23

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

1 Answers1