What does the alias after DELETE mean?

Question

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 DELETE p1?

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 Email from the both tables (p1 and p2) is not the same. Then we remove all the rows where the Id from the p2 is greater or equal than the Id from 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 Ids.

Is my understanding correct?

If it is correct, then DELETE p1 means delete all entries in Person where Id is in the resulting p1 part of the rows.

You may want to tag your DBMS as this syntax is not universally supported. — mustaccio, Mar 28 '21 at 20:32

score 1 · Accepted Answer · answered Mar 28 '21 at 20:25

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.

What does the alias after DELETE mean?

1 Answers1