All we need is an easy explanation of the problem, so here it is.
What does the alias after DELETE mean?
E.g. I have a query:
DELETE p1 FROM Person p1, Person p2 WHERE p1.Email = p2.Email AND p2.Id < p1.Id
What does it mean
I understand it as follows. First we find a cartesian product of the
Person table with itself. Then we remove all the rows where the
p2) is not the same. Then we remove all the rows where the
Id from the
p2 is greater or equal than the
p1. Now we are left with some rows. We pick all the
Ids from the
p1 part of the left rows and remove all the entries from the initial
Person table, which has the
Id contained in the picked
Is my understanding correct?
If it is correct, then
DELETE p1 means delete all entries in
Id is in the resulting
p1 part of the rows.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Imagine that the deletion is a process which consists from 2 steps. At the 1st step the server performs according
SELECT * selection, and marks the rows which must be deleted. At the 2nd step it deletes marked rows.
DELETE p1 in your query means that only rows selected from a table aliased as
p1 must be marked for deletion. If you’d use DELETE p1, p2 or simply DELETE FROM then selected rows from all table copies ‘d be marked.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂