Thanks
>>> <mfatene@stripped> 6/17/05 2:03:02 PM >>>
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 < EReed@stripped >:
> 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/mysql?unsub=1
