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
• Status: Not a Bug
• 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.