Bug #1480 Inconsistent handling for update/delete of subquery in FROM

• Submitted: 4 Oct 2003 17:18
• Modified: 5 Oct 2003 9:04
• Reporter: Peter Gulutzan
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.1
• OS: Linux (SuSE 8.2)
• CPU Architecture: Any

[4 Oct 2003 17:18] Peter Gulutzan

Description:
The statement "update (select * from t7) as t7 set s1 = 5;" 
should cause an error return, because the similar statement 
"delete from (select * from t7) as t7" causes an error 
return. 
 
(I can't think of a reason that one would want 
to update a subquery anyway.) 
 

How to repeat:
CREATE TABLE t7 (s1 INT); 
INSERT INTO t7 VALUES (1); 
UPDATE (SELECT * FROM t7) AS t7 SET s1 = 5; 
DELETE FROM (SELECT * FROM t7) AS t7; 
 

Suggested fix:
Make both UPDATE  illegal with subquery in FROM.

[5 Oct 2003 9:04] Peter Gulutzan

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

It turns out that Oleksandr Byelkin noted this bug before I did. See: 
Bug #1477 
update allow apdating of derived table