When I execute the query as mentioned below, it executes without any issues.
delete from table where entryID in
(
select * from (select max(entryID) from table where locationId = 2) as deleted
);
But, when I try to alter the same query as below, I see "Error Code: 1093. You can't specify target table 'table' for update in FROM clause". Why is it not accepting the below query?
delete from table where entryID in
(
select max(entryID) from table where locationId = 2
);
table is a reserved keyword (note the R next to TABLE), so it cannot be used for an identifer.
Use a different name for your table, or encapsulate your table name in backticks:
delete from `table` where entryID in
(
select max(entryID) from `table` where locationId = 2
);
It's a restriction in MySQL. The error message pretty well spells out what the restriction is. (This is documented somewhere in the MySQL Reference Manual.)
It may make more sense to understand the reason that first query doesn't return an error. Why that is a workaround to the restriction. And that's because of the way that MySQL processes it. The query inside the parens is an inline view, which MySQL calls a derived table. What MySQL calls it makes sense, when we understand how MySQL operates.
MySQL first runs the inline view query and materializes the results into a temporary(ish) derived table.
It's very similar to the effect of doing a CREATE TEMPORARY TABLE foo ... and an INSERT INTO foo SELECT ....
After that's all squared away, and we have a derived table, then the outer query runs. The outer query references the derived table. In the example given, the derived table is assigned the alias "deleted". Notice that the FROM clause is referencing the derived table. That is to say, it doesn't reference to the target of the DELETE. So it doesn't violate the restriction.
