Intercambio de valores de columna en MySQL

Tengo una tabla MySQL con coordenadas, los nombres de columna son X e Y. Ahora quiero intercambiar los valores de columna en esta tabla, de modo que X se convierta en Y e Y se convierta en X. La solución más aparente sería cambiar el nombre de las columnas, pero No quiero hacer cambios en la estructura ya que no necesariamente tengo permisos para hacerlo.

¿Es esto posible con UPDATE de alguna manera? La tabla ACTUALIZAR SET X = Y, Y = X obviamente no hará lo que yo quiero.


Editar: Tenga en cuenta que mi restricción de permisos, mencionada anteriormente, impide de manera efectiva el uso de ALTER TABLE u otros comandos que cambian la estructura de la tabla / base de datos. Cambiar el nombre de las columnas o agregar nuevas, desafortunadamente no son opciones.

Solo tuve que lidiar con lo mismo y resumiré mis hallazgos.

  1. La UPDATE table SET X=Y, Y=X enfoque obviamente no funciona, ya que simplemente establecerá ambos valores en Y.

  2. Aquí hay un método que usa una variable temporal. Gracias a Antonio por los comentarios de http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ para el ajuste “IS NOT NULL”. Sin él, la consulta funciona de manera impredecible. Vea el esquema de la tabla al final de la publicación. Este método no intercambia los valores si uno de ellos es NULL. Use el método n. ° 3 que no tenga esta limitación.

    UPDATE swap_test SET x=y, y=@temp WHERE (@temp:=x) IS NOT NULL;

  3. Este método fue ofrecido por Dipin en, una vez más, los comentarios de http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ . Creo que es la solución más elegante y limpia. Funciona con valores NULL y no NULL.

    UPDATE swap_test SET x=(@temp:=x), x = y, y = @temp;

  4. Otro enfoque que se me ocurrió parece funcionar:

    UPDATE swap_test s1, swap_test s2 SET s1.x=s1.y, s1.y=s2.x WHERE s1.id=s2.id;

Básicamente, la primera tabla es la que se actualiza y la segunda se utiliza para extraer los datos antiguos.
Tenga en cuenta que este enfoque requiere una clave primaria para estar presente.

Este es mi esquema de prueba:

 CREATE TABLE `swap_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `x` varchar(255) DEFAULT NULL, `y` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; INSERT INTO `swap_test` VALUES ('1', 'a', '10'); INSERT INTO `swap_test` VALUES ('2', NULL, '20'); INSERT INTO `swap_test` VALUES ('3', 'c', NULL); 

Puedes tomar la sum y restar el valor opuesto usando X e Y

 UPDATE swaptest SET X=X+Y,Y=XY,X=XY; 

Aquí hay una prueba de muestra (y funciona con números negativos)

 mysql> use test Database changed mysql> drop table if exists swaptest; Query OK, 0 rows affected (0.03 sec) mysql> create table swaptest (X int,Y int); Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO swaptest VALUES (1,2),(3,4),(-5,-8),(-13,27); Query OK, 4 rows affected (0.08 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM swaptest; +------+------+ | X | Y | +------+------+ | 1 | 2 | | 3 | 4 | | -5 | -8 | | -13 | 27 | +------+------+ 4 rows in set (0.00 sec) mysql> 

Aquí está el intercambio que se realiza

 mysql> UPDATE swaptest SET X=X+Y,Y=XY,X=XY; Query OK, 4 rows affected (0.07 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> SELECT * FROM swaptest; +------+------+ | X | Y | +------+------+ | 2 | 1 | | 4 | 3 | | -8 | -5 | | 27 | -13 | +------+------+ 4 rows in set (0.00 sec) mysql> 

Darle una oportunidad !!!

El siguiente código funciona para todos los escenarios en mi prueba rápida:

 UPDATE table swap_test SET x=(@temp:=x), x = y, y = @temp 

La tabla de ACTUALIZACIÓN SET X = Y, Y = X hará exactamente lo que quiera (editar: en PostgreSQL, no en MySQL, ver abajo). Los valores se toman de la fila anterior y se asignan a una nueva copia de la misma fila, luego se reemplaza la fila anterior. No tiene que recurrir al uso de una tabla temporal, una columna temporal u otros trucos de intercambio.

@ D4V360: Ya veo. Eso es impactante e inesperado. Yo uso PostgreSQL y mi respuesta funciona correctamente allí (lo intenté). Consulte los documentos de ACTUALIZACIÓN de PostgreSQL (en Parámetros, expresión), donde menciona que las expresiones en el lado derecho de las cláusulas SET usan explícitamente los valores anteriores de las columnas. Veo que los documentos de ACTUALIZACIÓN de MySQL correspondientes contienen la instrucción “Las asignaciones de UPDATE de una sola tabla generalmente se evalúan de izquierda a derecha”, lo que implica el comportamiento que describes.

Bueno saber.

Ok, solo por diversión, ¡podrías hacer esto! (suponiendo que está intercambiando valores de cadena)

 mysql> select * from swapper; +------+------+ | foo | bar | +------+------+ | 6 | 1 | | 5 | 2 | | 4 | 3 | +------+------+ 3 rows in set (0.00 sec) mysql> update swapper set -> foo = concat(foo, "###", bar), -> bar = replace(foo, concat("###", bar), ""), -> foo = replace(foo, concat(bar, "###"), ""); Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from swapper; +------+------+ | foo | bar | +------+------+ | 1 | 6 | | 2 | 5 | | 3 | 4 | +------+------+ 3 rows in set (0.00 sec) 

Un poco de diversión al abusar del proceso de evaluación de izquierda a derecha en MySQL.

Alternativamente, simplemente use XOR si son números. Mencionaste coordenadas, ¿tienes valores enteros encantadores o cadenas complejas?

Editar: Por cierto, las cosas de XOR funcionan así:

 update swapper set foo = foo ^ bar, bar = foo ^ bar, foo = foo ^ bar; 

Dos alternativas 1. Use una tabla temporal 2. Investigue el algoritmo XOR

 Tabla ALTER TABLE ADD COLUMN tmp;
 ACTUALIZAR tabla SET tmp = X;
 Tabla de ACTUALIZACIÓN SET X = Y;
 ACTUALIZAR tabla SET Y = tmp;
 Tabla ALTER TABLE DROP COLUMN tmp;

¿Algo como esto?

Editar: Acerca del comentario de Greg: No, esto no funciona:

 mysql> selecciona * de prueba;
 + ------ + ------ +
 |  x |  y |
 + ------ + ------ +
 |  1 |  2 |
 |  3 |  4 |
 + ------ + ------ +
 2 filas en el conjunto (0.00 seg) 

mysql> update test set x = y, y = x;
Consulta OK, 2 filas afectadas (0.00 seg)
Filas coincidentes: 2 Modificado: 2 Advertencias: 0

mysql> selecciona * de prueba;
+ —— + —— +
| x | y |
+ —— + —— +
| 2 | 2 |
| 4 | 4 |
+ —— + —— +
2 filas en el conjunto (0.00 seg)

Creo que tener una variable de intercambio intermedio es la mejor práctica de esa manera:

 update z set c1 = @c := c1, c1 = c2, c2 = @c 

Primero, funciona siempre; segundo, funciona independientemente del tipo de datos.

A pesar de ambos

 update z set c1 = c1 ^ c2, c2 = c1 ^ c2, c1 = c1 ^ c2 

y

 update z set c1 = c1 + c2, c2 = c1 - c2, c1 = c1 - c2 

están trabajando normalmente, solo para el tipo de datos numéricos por cierto, y es su responsabilidad evitar el desbordamiento, no puede usar XOR entre firmado y sin firmar, tampoco puede usar la sum para la posibilidad de desbordamiento.

Y

 update z set c1 = c2, c2 = @c where @c := c1 

no funciona si c1 es 0 o NULL o cadena de longitud cero o solo espacios.

Necesitamos cambiarlo a

 update z set c1 = c2, c2 = @c where if((@c := c1), true, true) 

Aquí están los guiones:

 mysql> create table z (c1 int, c2 int) -> ; Query OK, 0 rows affected (0.02 sec) mysql> insert into z values(0, 1), (-1, 1), (pow(2, 31) - 1, pow(2, 31) - 2) -> ; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from z; +------------+------------+ | c1 | c2 | +------------+------------+ | 0 | 1 | | -1 | 1 | | 2147483647 | 2147483646 | +------------+------------+ 3 rows in set (0.02 sec) mysql> update z set c1 = c1 ^ c2, c2 = c1 ^ c2, c1 = c1 ^ c2; ERROR 1264 (22003): Out of range value for column 'c1' at row 2 mysql> update z set c1 = c1 + c2, c2 = c1 - c2, c1 = c1 - c2; ERROR 1264 (22003): Out of range value for column 'c1' at row 3 mysql> select * from z; +------------+------------+ | c1 | c2 | +------------+------------+ | 0 | 1 | | 1 | -1 | | 2147483646 | 2147483647 | +------------+------------+ 3 rows in set (0.02 sec) mysql> update z set c1 = c2, c2 = @c where @c := c1; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from z; +------------+------------+ | c1 | c2 | +------------+------------+ | 0 | 1 | | -1 | 1 | | 2147483647 | 2147483646 | +------------+------------+ 3 rows in set (0.00 sec) mysql> select * from z; +------------+------------+ | c1 | c2 | +------------+------------+ | 1 | 0 | | 1 | -1 | | 2147483646 | 2147483647 | +------------+------------+ 3 rows in set (0.00 sec) mysql> update z set c1 = @c := c1, c1 = c2, c2 = @c; Query OK, 3 rows affected (0.02 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from z; +------------+------------+ | c1 | c2 | +------------+------------+ | 0 | 1 | | -1 | 1 | | 2147483647 | 2147483646 | +------------+------------+ 3 rows in set (0.00 sec) mysql>update z set c1 = c2, c2 = @c where if((@c := c1), true, true); Query OK, 3 rows affected (0.02 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from z; +------------+------------+ | c1 | c2 | +------------+------------+ | 1 | 0 | | 1 | -1 | | 2147483646 | 2147483647 | +------------+------------+ 3 rows in set (0.00 sec) 

¡Esto seguramente funciona! Solo lo he necesitado para cambiar las columnas de precios de Euro y SKK. 🙂

 UPDATE tbl SET X=Y, Y=@temp where @temp:=X; 

Lo anterior no funcionará (ERROR 1064 (42000): tiene un error en su syntax de SQL)

Suponiendo que ha firmado números enteros en sus columnas, puede necesitar usar CAST (a ^ b AS SIGNED), ya que el resultado del operador ^ es un entero de 64 bits sin signo en MySQL.

En caso de que ayude a alguien, aquí está el método que usé para cambiar la misma columna entre dos filas determinadas:

 SELECT BIT_XOR(foo) FROM table WHERE key = $1 OR key = $2 UPDATE table SET foo = CAST(foo ^ $3 AS SIGNED) WHERE key = $1 OR key = $2 

donde $ 1 y $ 2 son las claves de dos filas y $ 3 es el resultado de la primera consulta.

No lo he intentado pero

 UPDATE tbl SET @temp=X, X=Y, Y=@temp 

Podría hacerlo.

marca

Podrías cambiar los nombres de las columnas, pero esto es más un truco. Pero tenga cuidado con los índices que pueden estar en estas columnas

Intercambio de valores de columna con consulta única

ACTUALIZAR my_table SET a = @ tmp: = a, a = b, b = @ tmp;

aclamaciones…!

Solo tuve que mover el valor de una columna a la otra (como archivar) y restablecer el valor de la columna original.
El siguiente (referencia del # 3 de la respuesta aceptada arriba) funcionó para mí.

 Update MyTable set X= (@temp:= X), X = 0, Y = @temp WHERE ID= 999; 
 CREATE TABLE Names ( F_NAME VARCHAR(22), L_NAME VARCHAR(22) ); INSERT INTO Names VALUES('Ashutosh', 'Singh'),('Anshuman','Singh'),('Manu', 'Singh'); UPDATE Names N1 , Names N2 SET N1.F_NAME = N2.L_NAME , N1.L_NAME = N2.F_NAME WHERE N1.F_NAME = N2.F_NAME; SELECT * FROM Names;