Hi, There is one caveat: It is not currently possible to modify a table and select from the same table in a subquery.
this phrase is from http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html solution ********* create table t as Select field1 From table1 Where field2="Some Value" Update table1 Set field1=(Select field1 From t Where field2="Some Value") Where field2 ="Another Value"; ********** mysql> select * from upd; +------+------+ | a | b | +------+------+ | 1 | one | | 1 | two | | 2 | one | | 2 | two | | 1 | Un | +------+------+ 5 rows in set (0.09 sec) mysql> create table t as select * from upd where b='Un'; mysql> update upd set a=(select a from t where b='Un') where b='one'; mysql> select * from upd; +------+------+ | a | b | +------+------+ | 1 | one | | 1 | two | | 1 | one |<==== changed | 2 | two | | 1 | Un | +------+------+ 5 rows in set (0.00 sec) Mathias Selon Ed Reed <[EMAIL PROTECTED]>: > Can anyone tell me how I can make this work or suggest a work around? > > Update table1 Set field1=(Select field1 From table1 Where field2="Some > Value") > Where field2 ="Another Value"; > > Thanks > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
