What's wrong with this query?

Question

UPDATE `t` SET `col_x` = 
(SELECT `col_x` FROM `t` WHERE `col_y`='123456') WHERE `col_y`= '456789'

MySQL version 4.0.27

I tried few changes - with LIKE '%123456%', without backticks

mysql seems to not know subquery. it's error is "You have an error in your SQL syntax ..."

thanks

Error message: #1064 - 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 t FROM b WHERE col_x='1234

Answer 1

I'm sorry to disappoint you, but subqueries are not supported in your version if MySQL.

Subqueries have been introduced in ver 4.1 according to MySQL Dev Zone

Answer 2

You can't select and update from the same table in a query.

reference: mysql update documentation

Answer 3

Check out this page. Apparently you need to set your SQL mode to 'ANSI QUOTES'