Could you accomplish this with an update and self join?
Ed Reed wrote:
Sorry, I thought it was easy to understand.
I wanna update a field in a table with a value from the same field but from a different record of the same table.
For example, using the query in my original message,
+------------------------------+
|RecID |FIELD1 | FIELD2 |
+------------------------------+
| 1 | ABC | A Value |
+------------------------------+
| 2 | DEF | Some Value |
+------------------------------+
| 3 | GHI | Another Value |
+------------------------------+
| 4 | JKL | More Values |
+------------------------------+
Update table1 Set field1=(Select field1 From table1 Where field2="Some Value")
Where field2 ="Another Value";
This query should set FIELD1 of Record 3 to 'DEF'
+------------------------------+
|RecID |FIELD1 | FIELD2 |
+------------------------------+
| 1 | ABC | A Value |
+------------------------------+
| 2 | DEF | Some Value |
+------------------------------+
| 3 | DEF | Another Value |
+------------------------------+
| 4 | JKL | More Values |
+------------------------------+
That's it! Should be easy but I get an error that says "You can't specify target
table 'table1' for update in FROM clause"
- Thanks
<[EMAIL PROTECTED]> 6/17/05 11:03:40 AM >>>
"Ed Reed" < [EMAIL PROTECTED] > wrote on 06/17/2005 01:35:40 PM:
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
OK, your example must be broken (or it wouldn't be here) and since that's
all you posted I have a hard time divining your intent. Would you please
provide some sample data and explain what it is you want to do _in words_?
I can't speak for everyone but I know it would sure help me figure out
what you want to do.
Thanks!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]