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
