<cut>
mysql> select Nombre, Padre, idUsuario, url, min, hide, if
Jorge> (Nombre=Padre,0,1) as orden from umbLigasxUsr where
Jorge> idUsuario='dinosaurio' order by Padre, orden, Nombre;
Jorge>
> +--------------+----------+------------+---------------------+-----+------+-------+
Jorge> | Nombre | Padre | idUsuario | url | min |
Jorge> hide | orden |
Jorge>
> +--------------+----------+------------+---------------------+-----+------+-------+
Jorge> | Buscador | Buscador | dinosaurio | http:www.umbral.com | N |
Jorge> N | 0 |
Jorge> | Buscador_uno | Buscador | dinosaurio | http:www.umbral.com | N |
Jorge> N | 1 |
Jorge> | Busqueda | Buscador | dinosaurio | http:www.umbral.com | N |
Jorge> N | 1 |
Jorge> | Libro | Libro | dinosaurio | http:www.umbral.com | N |
Jorge> N | 0 |
Jorge> | Tumbaburros | Libro | dinosaurio | http:www.umbral.com | N |
Jorge> N | 1 |
Jorge>
> +--------------+----------+------------+---------------------+-----+------+-------+
Jorge> 5 rows in set (0.01 sec)
mysql> UPDATE umbLigasxUsr set
Jorge> Padre='Diccionario',Nombre=if(Padre=Nombre,'Diccionario',Nombre) where
Jorge> Padre='Libro';
Jorge> Query OK, 2 rows affected (0.00 sec)
Jorge> Rows matched: 2 Changed: 2 Warnings: 0
mysql> select Nombre, Padre, idUsuario, url, min, hide, if
Jorge> (Nombre=Padre,0,1) as orden from umbLigasxUsr where
Jorge> idUsuario='dinosaurio' order by Padre, orden, Nombre;
Jorge>
> +--------------+-------------+------------+---------------------+-----+------+-------+
Jorge> | Nombre | Padre | idUsuario | url | min |
Jorge> hide | orden |
Jorge>
> +--------------+-------------+------------+---------------------+-----+------+-------+
Jorge> | Buscador | Buscador | dinosaurio | http:www.umbral.com | N |
Jorge> N | 0 |
Jorge> | Buscador_uno | Buscador | dinosaurio | http:www.umbral.com | N |
Jorge> N | 1 |
Jorge> | Busqueda | Buscador | dinosaurio | http:www.umbral.com | N |
Jorge> N | 1 |
Jorge> | Libro | Diccionario | dinosaurio | http:www.umbral.com | N |
Jorge> N | 1 |
Jorge> | Tumbaburros | Diccionario | dinosaurio | http:www.umbral.com | N |
Jorge> N | 1 |
Jorge>
> +--------------+-------------+------------+---------------------+-----+------+-------+
Jorge> 5 rows in set (0.01 sec)
Jorge> As you can see, the field "Nombre" on the last result table doesn't
Jorge> change the word "Libro" on row 4 for the word "Diccionario". Could you
Jorge> explain it? I think I'm doing everything right!!! By the way, I'm using
Jorge> MYSQL 3.22.14b.
<cut>
Hi!
This is 'probably' a bug in MySQL (but I am not sure).
The problem is that all updates are done from right to left.
In the case:
UPDATE umbLigasxUsr set Padre='Diccionario',
Nombre=if(Padre=Nombre,'Diccionario',Nombre)
where Padre='Libro';
Padre is first updated to 'Diccionario'; After this the 'if' check
will fails becase Padre now contains the value 'Diccionario' and not
'Libro'.
Fix use:
UPDATE umbLigasxUsr set Nombre=if(Padre=Nombre,'Diccionario',Nombre),
Padre='Diccionario' where Padre='Libro';
Regards,
Monty
PS: I just checked the SQL standard, and it says that MySQL does wrong
in the case :(
We will have to change that in the future all columns
refer to the original row value. This will break all applications
that uses this feature in the INSERT statement (but I assume there
is very few applications that uses this).
