MySQL justification behind "Unable to specify target table for update in FROM clause"

Question

In MySQL, I cannot reuse a table in a predicate if I execute DELETEor UPDATEin the same table. For example, this is not possible:

DELETE FROM story_category WHERE category_id NOT IN (
--          ^^^^^^^^^^^^^^ deleting from this table...

SELECT DISTINCT category.id FROM category 
       INNER JOIN story_category ON category_id=category.id);
--                ^^^^^^^^^^^^^^ ... prevents using the same table in predicates

The above example was taken from this stack overflow question , where the accepted answer and many other answers indicate that there is a trick that you can use by nesting a subquery in another subquery to prevent MySQL from recognizing table reuse:

DELETE FROM story_category
--          ^^^^^^^^^^^^^^
WHERE category_id NOT IN (
    SELECT cid FROM (
        SELECT DISTINCT category.id AS cid FROM category 
        INNER JOIN story_category ON category_id=category.id
--                 ^^^^^^^^^^^^^^ apparently no longer considered the same table
    ) AS c
)

Now this is a workaround and the fact that it is even possible raises two big questions:

• . , MySQL , () .
• , , , , .

, , MySQL ACID-ness. , , , .

, MySQL :

.

, :

, MySQL , UPDATE DELETE ?

Answer 1

, , , , "" . , , - .

"" :

UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS dt ...);

, UPDATE MySQL, , - : UPDATE, SELECT. SELECT , "" , .

, , , InnoDB , , , PostgreSQL, Oracle .

, , , MyISAM, , , .