List:General Discussion« Previous MessageNext Message »
From:Ed Reed Date:June 17 2005 11:46pm
Subject:Re: Subselect in an Update query
View as plain text  
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 



Thread
Subselect in an Update queryEd Reed17 Jun
  • Re: Subselect in an Update querySGreen17 Jun
  • Re: Subselect in an Update querymfatene18 Jun
    • Re: Subselect in an Update queryJochem van Dieten18 Jun
Re: Subselect in an Update queryEd Reed18 Jun
  • Re: Subselect in an Update queryEric Bergen18 Jun
Re: Subselect in an Update queryEd Reed18 Jun
Re: Subselect in an Update queryEd Reed18 Jun