What's wrong with this query?

using MySQL version 4.开发者_开发知识库0.27:

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

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

I tried LIKE '%123456%'

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

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

reference: mysql update documentation

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