Description:
The syntax description for single table UPDATE does not reflect that it is valid to use an alias for the table name.
Both the DELETE and UPDATE syntax descriptions say "tbl_name" for the case of the single table variants of the statements, but UPDATE allows a full table reference, even for the single table command.
The description for DELETE is correct and it does not allow an alias.
The relevant portion of the YACC grammar for UPDATE (note the join_table_list):
update:
UPDATE_SYM
{
LEX *lex= Lex;
mysql_init_select(lex);
lex->sql_command= SQLCOM_UPDATE;
lex->lock_option= TL_UNLOCK; /* Will be set later */
lex->duplicates= DUP_ERROR;
}
opt_low_priority opt_ignore join_table_list
SET update_list
How to repeat:
CREATE TABLE `test` (
`a` int(11) DEFAULT NULL
)
mysql> delete from test as t where t.a is null;
ERROR 1064 (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 'as t where t.a is null' at line 1
mysql> update test as t set a=1 where t.a is null;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
To prove that the above query is not using the multi-table codepath with the cornercase of one table, add a LIMIT clause (which is not allowed for multi-table updates):
mysql> update test1 as t set a=1 where t.a is null limit 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
Suggested fix:
Change the description of UPDATE to use `table_reference` instead of `tbl_name` for the single table variant.
Description: The syntax description for single table UPDATE does not reflect that it is valid to use an alias for the table name. Both the DELETE and UPDATE syntax descriptions say "tbl_name" for the case of the single table variants of the statements, but UPDATE allows a full table reference, even for the single table command. The description for DELETE is correct and it does not allow an alias. The relevant portion of the YACC grammar for UPDATE (note the join_table_list): update: UPDATE_SYM { LEX *lex= Lex; mysql_init_select(lex); lex->sql_command= SQLCOM_UPDATE; lex->lock_option= TL_UNLOCK; /* Will be set later */ lex->duplicates= DUP_ERROR; } opt_low_priority opt_ignore join_table_list SET update_list How to repeat: CREATE TABLE `test` ( `a` int(11) DEFAULT NULL ) mysql> delete from test as t where t.a is null; ERROR 1064 (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 'as t where t.a is null' at line 1 mysql> update test as t set a=1 where t.a is null; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 To prove that the above query is not using the multi-table codepath with the cornercase of one table, add a LIMIT clause (which is not allowed for multi-table updates): mysql> update test1 as t set a=1 where t.a is null limit 1; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 Suggested fix: Change the description of UPDATE to use `table_reference` instead of `tbl_name` for the single table variant.