Thanks for the reply. What do you mean by 'self join'?
>>> Eric Bergen <[EMAIL PROTECTED]> 6/17/05 4:39:28 PM >>> 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]
