What does the alias after DELETE mean

delete

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 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.

Best Answer

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.

Related Solutions

Mysql – remove duplicate rows in thesql table that does not contain primary key

In the spirit of @yercube's answer, I have an answer that has an added twist.

CREATE TABLE stage
(
    id int not null auto_increment,
    name varchar(20),
    primary key (id)
);
CREATE TABLE stage2 LIKE stage;
INSERT INTO stage (name) SELECT name FROM item;
INSERT INTO stage2 (id) SELECT min_id FROM
(SELECT MIN(id) min_id,name FROM stage GROUP BY name) A;
UPDATE stage2 A INNER JOIN stage B USING (id) SET A.name=B.name;
TRUNCATE TABLE item;
INSERT INTO item (name) SELECT name FROM stage2;
DROP TABLE stage;
DROP TABLE stage2;

This will load stage2 with the first occurrence of each name from item, zap the item table, and load the unique occurrences back.

If you look back in @yercube's answer and compare it to my answer, his is much more simplistic because

@yercube uses one temp table, while I use two
I had to create a column for iteration control, @yercube did not need to
@yercube has fewer steps
both answers achieve the same thing

I do not expect my answer to be accepted. The sole purpose of my answer was demonstrate that other answers lose the concise clarity needed to solve your problem. Again, hats off to @yercube.

Delete all rows within transaction: what would happen to other transactions

The comment by a_horse_with_no_name is actually the answer, but since it was not posted as an answer, I'm not able to mark it so. I've completely forgotten about transaction isolation levels, I've asked the question in VoltDB forums and it appears that as long as make sure that stored procedures are local to nodes (sorry, this is a bit of a VoltDB terminology), that is, they don't process data across partitioned tables, this should work.

Best Answer

Related Solutions

Mysql – remove duplicate rows in thesql table that does not contain primary key

Delete all rows within transaction: what would happen to other transactions

Related Question