On Tue, Jul 15, 2008 at 7:59 AM, Guenter Ladstaetter <[EMAIL PROTECTED]> wrote: > UPDATE `phpbt_bug` > SET title = REPLACE (phpbt_bug.title,"ö","ö") > WHERE bug_id IN > (select bug_id, title from phpbt_bug where `title` LIKE "%ö%"); > > > The error message is: Operand should contain 1 column(s) 1241
You have multiple problems. 1. IN() subqueries only allow one column, and you have two. Take a look at http://dev.mysql.com/doc/refman/5.0/en/comparisons-using-subqueries.html . Please note that in general sub queries are a less than good idea in MySQL 2. "Currently, you cannot update a table and select from the same table in a subquery." http://dev.mysql.com/doc/refman/5.0/en/update.html mysql> create table phpbt_bug(title varchar(255),bug_id int); Query OK, 0 rows affected (0.25 sec) mysql> UPDATE `phpbt_bug` -> SET title = REPLACE (phpbt_bug.title,"ö","A¶") -> WHERE bug_id IN -> (select bug_id, title from phpbt_bug where `title` LIKE "%ö%"); ERROR 1241 (21000): Operand should contain 1 column(s) So you would want to get rid of title to deal with that error. mysql> UPDATE `phpbt_bug` -> SET title = REPLACE (phpbt_bug.title,"ö","A¶") -> WHERE bug_id IN -> (select bug_id from phpbt_bug where `title` LIKE "%ö%"); ERROR 1093 (HY000): You can't specify target table 'phpbt_bug' for update in FROM clause Now your just SOL with this strategy. Lets get rid of the subquery, which should generally be avoided in mysql anyways. mysql> UPDATE `phpbt_bug` -> SET title = REPLACE (phpbt_bug.title,"ö","A¶") -> WHERE `title` LIKE "%ö%"; Query OK, 0 rows affected (0.34 sec) Rows matched: 0 Changed: 0 Warnings: 0 And this works mysql versions going back forever. You should not be gaining anything from the where clause, one way or another every row will need to be examined. mysql> UPDATE `phpbt_bug` -> SET title = REPLACE (phpbt_bug.title,"ö","A¶"); Query OK, 0 rows affected (0.00 sec) Note the rows affected. If the update does not change anything in a row the number will not be incremented. -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
