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'