Re: Subselect in an Update query
On 6/17/05, [EMAIL PROTECTED] wrote: There is one caveat: It is not currently possible to modify a table and select from the same table in a subquery. That is not the only problem: there is no guarantee the subquery will only return one record. So even if MySQL wouldn't have this limitation you would still run the risk of an executor error when the subquery returns more then one record. Try this: UPDATE table1 a, table2 b SET a.field1 = b.field1 WHERE b.field2 = 'Some Value' AND a.field2 = 'Another Value' Jochem PS Please use single quotes to delimit strings, sticking to the SQL standard makes it easier to read. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subselect in an Update query
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
Re: Subselect in an Update query
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
Re: Subselect in an Update query
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 [EMAIL PROTECTED]: 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/[EMAIL PROTECTED]
Re: Subselect in an Update query
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
Re: Subselect in an Update query
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]
Re: Subselect in an Update query
Thanks [EMAIL PROTECTED] 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 [EMAIL PROTECTED] : 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/[EMAIL PROTECTED]
Re: Subselect in an Update query
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]
