Bug #3327 same-table subqueries don't work in DELETE statements

• Submitted: 29 Mar 2004 12:53
• Modified: 30 Mar 2004 8:37
• Reporter: Nathan Jensen
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.1.1a-alpha-max
• OS: Windows (Windows)
• CPU Architecture: Any

[29 Mar 2004 12:53] Nathan Jensen

Description:
A subquery as part of a DELETE throws an error.

Error Code : 1093
You can't specify target table 'defaultfolders' for update in FROM clause

How to repeat:
DELETE FROM DefaultFolders 
WHERE DefaultFolderID NOT IN 
(SELECT DISTINCT DefaultParentID FROM DefaultFolders)

Error Code : 1093
You can't specify target table 'defaultfolders' for update in FROM clause

Suggested fix:
This syntax is common sql and should be supported.

[29 Mar 2004 13:56] Nathan Jensen

same-table is the problem.

this works:
DELETE FROM Notes
WHERE TopicID NOT IN
(SELECT TopicID FROM Topics);

. . .but this doesn't:
DELETE FROM DefaultFolders
WHERE DefaultParentID NOT IN
(SELECT DefaultFolderID FROM DefaultFolders);

[30 Mar 2004 6:16] MySQL Verification Team

The message error is clear:

Error Code : 1093
You can't specify target table 'defaultfolders' for update in FROM clause

[30 Mar 2004 8:37] Oleksandr Byelkin

The problem with subquery is following: 
we execute subquery only on demand it allow do not execute subquery in some 
cases where AND/OR condition prevent its execution. 
 
Because of above that query can be executed when half of table already deleted 
which is wrong. For now we just blocked ability to use table which is updated, 
i.e. it is limitation of implementation. 
 
of course we have plans to remove this limitation in cases like this, but we 
have not dead line for this task yet.