Bug #6162 Can't use subquery select statement in update statement
Submitted: 19 Oct 2004 20:03 Modified: 19 Oct 2004 20:32
Reporter: Vladimir Loubenski Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.21 OS:Windows (Windows)
Assigned to: Dean Ellis CPU Architecture:Any

[19 Oct 2004 20:03] Vladimir Loubenski
Description:
We need to use a result of select statemnet in update statement for example:
 UPDATE Table1 SET F1 = ( SELECT  F1 FROM Table2)
I get syntax error mesage from mysql.

Thank You.
Vladimir.

How to repeat:
In Mysql:
create Table1(F1 int);
create Table2(F1 int);
UPDATE Table1 SET F1 = ( SELECT  F1 FROM Table2);
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'SELECT  F1 F
ROM Table2)' at line 1
[19 Oct 2004 20:32] Dean Ellis
Subquery support was introduced in 4.1; for 4.0 you would need to rewrite the query using multi-table update syntax, or with multiple statements.
[19 Oct 2004 21:57] Vladimir Loubenski
Hi Dean,
I installed MySQL 4.1.6 -gamma server.
I use MySql 3.51.09 ODBC driver to connect to server. It is latest ODBC driver that I found. When I try to connect to server I get error message:
"Client does not support authentication protocol requested by server; consider upgrating MySQL client"
I don't mind to upgrate ODBC driver but when can I take it?
Thank you,
Vladimir.