From fbf563e86a219d02fa9bae0c8fb52c34c60bacbe Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 4 Jan 2004 23:44:33 +0200 Subject: 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 include/mysqld_error.h: new error message about non-updateable table mysql-test/r/derived.result: test of multi-update and multi-delete mysql-test/t/derived.test: test of multi-update and multi-delete sql/share/czech/errmsg.txt: new error message about non-updateable table sql/share/danish/errmsg.txt: new error message about non-updateable table sql/share/dutch/errmsg.txt: new error message about non-updateable table sql/share/english/errmsg.txt: new error message about non-updateable table sql/share/estonian/errmsg.txt: new error message about non-updateable table sql/share/french/errmsg.txt: new error message about non-updateable table sql/share/german/errmsg.txt: new error message about non-updateable table sql/share/greek/errmsg.txt: new error message about non-updateable table sql/share/hungarian/errmsg.txt: new error message about non-updateable table sql/share/italian/errmsg.txt: new error message about non-updateable table sql/share/japanese/errmsg.txt: new error message about non-updateable table sql/share/korean/errmsg.txt: new error message about non-updateable table sql/share/norwegian-ny/errmsg.txt: new error message about non-updateable table sql/share/norwegian/errmsg.txt: new error message about non-updateable table sql/share/polish/errmsg.txt: new error message about non-updateable table sql/share/portuguese/errmsg.txt: new error message about non-updateable table sql/share/romanian/errmsg.txt: new error message about non-updateable table sql/share/russian/errmsg.txt: new error message about non-updateable table sql/share/serbian/errmsg.txt: new error message about non-updateable table sql/share/slovak/errmsg.txt: new error message about non-updateable table sql/share/spanish/errmsg.txt: new error message about non-updateable table sql/share/swedish/errmsg.txt: new error message about non-updateable table sql/share/ukrainian/errmsg.txt: new error message about non-updateable table sql/sql_parse.cc: allow delete table by alias separate error message for try to delete derived table sql/sql_update.cc: test "is updated table derived?" sql/sql_yacc.yy: error message in case of try to update derived table --- mysql-test/r/derived.result | 27 ++++++++++++++++++++++++++- 1 file changed, 26 insertions(+), 1 deletion(-) (limited to 'mysql-test/r/derived.result') diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index bb268cd1094..f311b56f519 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -209,7 +209,7 @@ ERROR 42000: You have an error in your SQL syntax. Check the manual that corres create table t1 (a int); insert into t1 values (1),(2),(3); update (select * from t1) as t1 set a = 5; -ERROR 42000: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use +ERROR HY000: The target table t1 of the UPDATE is not updatable. delete from (select * from t1); ERROR 42000: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(select * from t1)' at line 1 insert into (select * from t1) values (5); @@ -245,3 +245,28 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DERIVED t1 ALL NULL NULL NULL NULL 2 3 UNION t1 ALL NULL NULL NULL NULL 2 drop table t1; +CREATE TABLE `t1` ( +`N` int(11) unsigned NOT NULL default '0', +`M` tinyint(1) default '0', +) TYPE=MyISAM DEFAULT CHARSET=latin1; +Warnings: +Warning 1286 'TYPE=database_engine' is deprecated. Use 'ENGINE=database_engine' instead. +INSERT INTO `t1` (N, M) VALUES (1, 0),(1, 0),(1, 0),(2, 0),(2, 0),(3, 0); +UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2; +select * from t1; +N M +1 2 +1 2 +1 2 +2 2 +2 2 +3 0 +UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2, P2.N = 2; +ERROR HY000: The target table P2 of the UPDATE is not updatable. +delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; +select * from t1; +N M +3 0 +delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; +ERROR HY000: The target table P2 of the DELETE is not updatable. +drop table t1; -- cgit v1.2.1