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

29 files changed, 132 insertions, 14 deletions

diff --git a/include/mysqld_error.h b/include/mysqld_error.h
index 6a484d0419c..179a2f823d7 100644
--- a/include/mysqld_error.h
+++ b/include/mysqld_error.h
@@ -303,4 +303,5 @@
 #define ER_WARN_HOSTNAME_WONT_WORK 1284
 #define ER_UNKNOWN_STORAGE_ENGINE 1285
 #define ER_WARN_DEPRECATED_SYNTAX 1286
-#define ER_ERROR_MESSAGES 287
+#define ER_NON_UPDATABLE_TABLE 1287
+#define ER_ERROR_MESSAGES 288
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;
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index caf673d95c1..77e76d16772 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -115,7 +115,7 @@ select mail_id,  if(folder.f_description!='', folder.f_description, folder.f_nam
 #
 create table t1 (a int);
 insert into t1 values (1),(2),(3);
--- error 1149
+-- error 1287
 update (select * from t1) as t1 set a = 5;
 -- error 1064
 delete from (select * from t1);
@@ -138,3 +138,22 @@ insert into t1 values (1),(2);
 select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
 explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
 drop table t1;
+
+
+#
+# multi-update & multi-delete with derived tables
+#
+CREATE TABLE `t1` (
+  `N` int(11) unsigned NOT NULL default '0',
+  `M` tinyint(1) default '0',
+) TYPE=MyISAM DEFAULT CHARSET=latin1;
+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;
+-- error 1287
+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;
+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;
+-- error 1287
+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;
+drop table t1;
diff --git a/sql/share/czech/errmsg.txt b/sql/share/czech/errmsg.txt
index 85606a060e7..cf5f573774b 100644
--- a/sql/share/czech/errmsg.txt
+++ b/sql/share/czech/errmsg.txt
@@ -299,3 +299,4 @@ character-set=latin2
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/danish/errmsg.txt b/sql/share/danish/errmsg.txt
index d005150cb89..2a42570ded3 100644
--- a/sql/share/danish/errmsg.txt
+++ b/sql/share/danish/errmsg.txt
@@ -293,3 +293,4 @@ character-set=latin1
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/dutch/errmsg.txt b/sql/share/dutch/errmsg.txt
index 3fd44f9ec69..53463c31d02 100644
--- a/sql/share/dutch/errmsg.txt
+++ b/sql/share/dutch/errmsg.txt
@@ -301,3 +301,4 @@ character-set=latin1
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/english/errmsg.txt b/sql/share/english/errmsg.txt
index afce57a9a74..71c8c8f1559 100644
--- a/sql/share/english/errmsg.txt
+++ b/sql/share/english/errmsg.txt
@@ -290,3 +290,4 @@ character-set=latin1
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/estonian/errmsg.txt b/sql/share/estonian/errmsg.txt
index dcdc74f5ab8..fecf0cde7be 100644
--- a/sql/share/estonian/errmsg.txt
+++ b/sql/share/estonian/errmsg.txt
@@ -295,3 +295,4 @@ character-set=latin7
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/french/errmsg.txt b/sql/share/french/errmsg.txt
index 94922ca40f4..afc1879a526 100644
--- a/sql/share/french/errmsg.txt
+++ b/sql/share/french/errmsg.txt
@@ -290,3 +290,4 @@ character-set=latin1
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/german/errmsg.txt b/sql/share/german/errmsg.txt
index 74f50289d86..56e263bc64e 100644
--- a/sql/share/german/errmsg.txt
+++ b/sql/share/german/errmsg.txt
@@ -302,3 +302,4 @@ character-set=latin1
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/greek/errmsg.txt b/sql/share/greek/errmsg.txt
index 5813717f57a..1596b1adbab 100644
--- a/sql/share/greek/errmsg.txt
+++ b/sql/share/greek/errmsg.txt
@@ -290,3 +290,4 @@ character-set=greek
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/hungarian/errmsg.txt b/sql/share/hungarian/errmsg.txt
index 77ce0b8d4d2..bcd17fca7cc 100644
--- a/sql/share/hungarian/errmsg.txt
+++ b/sql/share/hungarian/errmsg.txt
@@ -292,3 +292,4 @@ character-set=latin2
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/italian/errmsg.txt b/sql/share/italian/errmsg.txt
index 204e1f980cc..22a09ac1a1b 100644
--- a/sql/share/italian/errmsg.txt
+++ b/sql/share/italian/errmsg.txt
@@ -290,3 +290,4 @@ character-set=latin1
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/japanese/errmsg.txt b/sql/share/japanese/errmsg.txt
index af22a2eb49a..b76fa5d8f6d 100644
--- a/sql/share/japanese/errmsg.txt
+++ b/sql/share/japanese/errmsg.txt
@@ -292,3 +292,4 @@ character-set=ujis
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/korean/errmsg.txt b/sql/share/korean/errmsg.txt
index 6e348736698..482bc91852c 100644
--- a/sql/share/korean/errmsg.txt
+++ b/sql/share/korean/errmsg.txt
@@ -290,3 +290,4 @@ character-set=euckr
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/norwegian-ny/errmsg.txt b/sql/share/norwegian-ny/errmsg.txt
index 248d88bd1c9..a442a5879b8 100644
--- a/sql/share/norwegian-ny/errmsg.txt
+++ b/sql/share/norwegian-ny/errmsg.txt
@@ -292,3 +292,4 @@ character-set=latin1
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/norwegian/errmsg.txt b/sql/share/norwegian/errmsg.txt
index 9055b84f5e9..47660e29676 100644
--- a/sql/share/norwegian/errmsg.txt
+++ b/sql/share/norwegian/errmsg.txt
@@ -292,3 +292,4 @@ character-set=latin1
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/polish/errmsg.txt b/sql/share/polish/errmsg.txt
index ec9f3782b08..7f81facf6f5 100644
--- a/sql/share/polish/errmsg.txt
+++ b/sql/share/polish/errmsg.txt
@@ -294,3 +294,4 @@ character-set=latin2
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/portuguese/errmsg.txt b/sql/share/portuguese/errmsg.txt
index ffb70632bd4..8ddd281b346 100644
--- a/sql/share/portuguese/errmsg.txt
+++ b/sql/share/portuguese/errmsg.txt
@@ -291,3 +291,4 @@ character-set=latin1
 "MySQL foi inicializado em modo --skip-name-resolve. Você necesita reincializá-lo sem esta opção para este grant funcionar",
 "Motor de tabela desconhecido '%s'",
 "'%s' é desatualizado. Use '%s' em seu lugar.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/romanian/errmsg.txt b/sql/share/romanian/errmsg.txt
index e3640fb7a7a..2235871738c 100644
--- a/sql/share/romanian/errmsg.txt
+++ b/sql/share/romanian/errmsg.txt
@@ -294,3 +294,4 @@ character-set=latin2
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/russian/errmsg.txt b/sql/share/russian/errmsg.txt
index 5224f19b001..76fc04f42e4 100644
--- a/sql/share/russian/errmsg.txt
+++ b/sql/share/russian/errmsg.txt
@@ -292,3 +292,4 @@ character-set=koi8r
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"ôÁÂÌÉÃÁ %-.100s × %s ÎÅ ÍÏÖÅÔ ÉÚÍÅÎÑÔÓÑ.",
diff --git a/sql/share/serbian/errmsg.txt b/sql/share/serbian/errmsg.txt
index 904c8f0c8b9..a61c6a25ccc 100644
--- a/sql/share/serbian/errmsg.txt
+++ b/sql/share/serbian/errmsg.txt
@@ -285,3 +285,4 @@ character-set=cp1250
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/slovak/errmsg.txt b/sql/share/slovak/errmsg.txt
index c7e543a5497..36437930095 100644
--- a/sql/share/slovak/errmsg.txt
+++ b/sql/share/slovak/errmsg.txt
@@ -298,3 +298,4 @@ character-set=latin2
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/spanish/errmsg.txt b/sql/share/spanish/errmsg.txt
index 845a7da36eb..8e61ce6a62f 100644
--- a/sql/share/spanish/errmsg.txt
+++ b/sql/share/spanish/errmsg.txt
@@ -292,3 +292,4 @@ character-set=latin1
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/swedish/errmsg.txt b/sql/share/swedish/errmsg.txt
index 9da0a511ddb..9c13347cc3c 100644
--- a/sql/share/swedish/errmsg.txt
+++ b/sql/share/swedish/errmsg.txt
@@ -290,3 +290,4 @@ character-set=latin1
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"The target table %-.100s of the %s is not updatable.",
diff --git a/sql/share/ukrainian/errmsg.txt b/sql/share/ukrainian/errmsg.txt
index 7186e0550b2..b5d5f74ef77 100644
--- a/sql/share/ukrainian/errmsg.txt
+++ b/sql/share/ukrainian/errmsg.txt
@@ -295,3 +295,4 @@ character-set=koi8u
 "MySQL is started in --skip-name-resolve mode. You need to restart it without this switch for this grant to work",
 "Unknown table engine '%s'",
 "'%s' is deprecated. Use '%s' instead.",
+"ôÁÂÌÉÃÑ %-.100s Õ %s ÎÅ ÍÏÖÅ ÏÎÏ×ÌÀ×ÁÔÉÓØ.",
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 465d840e2b8..4310f69d2a7 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2663,15 +2663,30 @@ mysql_execute_command(THD *thd)
       table_count++;
       /* All tables in aux_tables must be found in FROM PART */
       TABLE_LIST *walk;
-      for (walk=(TABLE_LIST*) tables ; walk ; walk=walk->next)
+      for (walk= (TABLE_LIST*) tables; walk; walk= walk->next)
       {
-	if (!strcmp(auxi->real_name,walk->real_name) &&
+	if ((!strcmp(auxi->real_name,walk->real_name) ||
+	     !strcmp(auxi->real_name,walk->alias)) &&
 	    !strcmp(walk->db,auxi->db))
 	  break;
       }
       if (!walk)
       {
-	net_printf(thd,ER_NONUNIQ_TABLE,auxi->real_name);
+	if (lex->derived_tables)
+	{
+	  // are we trying to delete derived table?
+	  for (walk= (TABLE_LIST*) tables; walk; walk= walk->next)
+	  {
+	    if (!strcmp(auxi->real_name,walk->alias) &&
+		walk->derived)
+	    {
+	      net_printf(thd, ER_NON_UPDATABLE_TABLE,
+			 auxi->real_name, "DELETE");
+	      goto error;
+	    }
+	  }
+	}
+	net_printf(thd, ER_NONUNIQ_TABLE, auxi->real_name);
 	goto error;
       }
       walk->lock_type= auxi->lock_type;
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index cdea32ad3f6..87421c482d5 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -434,13 +434,36 @@ int mysql_multi_update(THD *thd,
   fix_tables_pointers(thd->lex->all_selects_list);
 
   select_lex->select_limit= HA_POS_ERROR;
+
+  table_map item_tables= 0, derived_tables= 0;
+  if (thd->lex->derived_tables)
+  {
+    // Assign table map values to check updatability of derived tables
+    uint tablenr=0;
+    for (TABLE_LIST *table_list= (TABLE_LIST*) select_lex->table_list.first;
+	 table_list;
+	 table_list= table_list->next, tablenr++)
+    {
+      table_list->table->map= (table_map) 1 << tablenr;
+    }
+  }
   if (setup_fields(thd, 0, table_list, *fields, 1, 0, 0))
     DBUG_RETURN(-1);
+  if (thd->lex->derived_tables)
+  {
+    // Find tables used in items
+    List_iterator_fast<Item> it(*fields);
+    Item *item;
+    while ((item= it++))
+    {
+      item_tables|= item->used_tables();
+    }
+  }
 
   /*
     Count tables and setup timestamp handling
   */
-  for (tl= select_lex->get_table_list() ; tl ; tl=tl->next)
+  for (tl= select_lex->get_table_list() ; tl ; tl= tl->next)
   {
     TABLE *table= tl->table;
     if (table->timestamp_field)
@@ -450,6 +473,18 @@ int mysql_multi_update(THD *thd,
       if (table->timestamp_field->query_id != thd->query_id)
 	table->time_stamp= table->timestamp_field->offset() +1;
     }
+    if (tl->derived)
+      derived_tables|= table->map;
+  }
+  if (thd->lex->derived_tables && (item_tables & derived_tables))
+  {
+    // find derived table which cause error
+    for (tl= select_lex->get_table_list() ; tl ; tl= tl->next)
+    {
+      if (tl->derived && (item_tables & tl->table->map))
+	my_printf_error(ER_NON_UPDATABLE_TABLE, ER(ER_NON_UPDATABLE_TABLE),
+			MYF(0), tl->alias, "UPDATE");
+    }
   }
 
   if (!(result=new multi_update(thd, table_list, fields, values,
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 310d8a41be2..41fb93891fb 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -3155,13 +3155,6 @@ join_table:
         | '(' SELECT_SYM select_derived ')' opt_table_alias
 	{
 	  LEX *lex=Lex;
-	  if (lex->sql_command == SQLCOM_UPDATE &&
-	      &lex->select_lex == lex->current_select->outer_select())
-	  {
-	    send_error(lex->thd, ER_SYNTAX_ERROR);
-	    YYABORT;
-	  }
-
 	  SELECT_LEX_UNIT *unit= lex->current_select->master_unit();
 	  lex->current_select= unit->outer_select();
 	  if (!($$= lex->current_select->
@@ -3838,6 +3831,13 @@ update:
 	  Select->set_lock_for_tables($3);
           if (lex->select_lex.table_list.elements > 1)
             lex->sql_command= SQLCOM_UPDATE_MULTI;
+	  else if (lex->select_lex.get_table_list()->derived)
+	  {
+	    /* it is single table update and it is update of derived table */
+	    net_printf(lex->thd, ER_NON_UPDATABLE_TABLE,
+		       lex->select_lex.get_table_list()->alias, "UPDATE");
+	    YYABORT;
+	  }
 	}
 	;