Hello,

i'm trying to replace german umlauts by using a simple update replace statement
but can't find the syntax error:

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

On Tue, Jul 15, 2008 at 7:59 AM, Guenter Ladstaetter
<albertino13@gmail.com> 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/...bqueries..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