Bug #2117 UPDATE with subquery
Submitted: 15 Dec 2003 10:18 Modified: 20 Jan 2004 11:24
Reporter: Jordi Garcia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.1 OS:Windows (Windows)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[15 Dec 2003 10:18] Jordi Garcia
Description:
From version 4.1.1-alpha it seems to be not allowed to use UPDATE with a subquery.

If you try my example you'll get: 

[Zeus] ERROR 1149: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use

Notice that the subquery is not intended to be updated. The same query was legal on version 4.1.0-alpha.

Is this feature discontinued? I hope is not!

How to repeat:
CREATE TABLE `p` (
  `N` int(11) unsigned NOT NULL default '0',
  `M` tinyint(1) default '0',
) TYPE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `p`(N, M) VALUES (1, 0);
INSERT INTO `p`(N, M) VALUES (1, 0);
INSERT INTO `p`(N, M) VALUES (1, 0);
INSERT INTO `p`(N, M) VALUES (2, 0);
INSERT INTO `p`(N, M) VALUES (2, 0);
INSERT INTO `p`(N, M) VALUES (3, 0);

UPDATE `p` AS P1 INNER JOIN (SELECT N FROM `p` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.NHC = P2.NHC
SET P1.M = 2;

Suggested fix:
Don't know...
[15 Dec 2003 10:46] Dean Ellis
Verified in 4.1.2.  Notice that the error message itself is unusual.

Thank you.
[15 Dec 2003 10:52] MySQL Verification Team
This is not an UPDATE with sub-selects.

This is an UPDATE with derived tables.

multi-table UPDATEs with derived tables in the join table list will not be 
supported until further notice.
[15 Dec 2003 12:32] Jordi Garcia
Hi again,

I'm sure you're very busy but as long as there's nothing I can do about that (except for switching back to version 4.1.0), can you tell me how long this issue will be deferred? or may be anyone can give me another solution?

Thank you in advance.
[2 Jan 2004 7:27] Sergei Golubchik
In MySQL 4.1.0 there was no check for derived table in UPDATE at all, thus one could've written

UPDATE (SELECT SIN(x) as y FROM a) b SET b.y=10;

which is obviously wrong. It was fixed.
But looks like now the syntax is too strict, and excludes some valid (and useful) usages of derived tables in the UPDATE statement. We'll try to fix it and make the check more specific.
[4 Jan 2004 13:46] Oleksandr Byelkin
ChangeSet 
  1.1675 04/01/04 23:44:33 bell@sanja.is.com.ua +29 -0 
  allow UPDATE and DELETE stetements with tables derived from subquery if they 
are not updated (BUG#2117) 
  allow delete table by alias in multi-delete statement
[20 Jan 2004 11:24] Oleksandr Byelkin
Thank You for your bugreport. Bugfix is now pushed in our sorce repository and will be present in next 4.1 release.