español - Intercambio de valores de columna en MySQL

mysql tutorial

Question

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.

Answer 1

¡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 sintaxis de SQL)

Answer 2

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 suma 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)

Answer 3

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

Answer 4

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

Answer 5

Intercambio de valores de columna con consulta única

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

aclamaciones...!

Answer 6

No lo he intentado pero

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

Podría hacerlo.

marca

Answer 7

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;

Answer 8

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

Answer 9

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

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

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=X-Y,X=X-Y;
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 !!!

Answer 10

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);

Answer 11

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;

Answer 12

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.

Answer 13

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.

Answer 14

ALTER TABLE table ADD COLUMN tmp; UPDATE table SET tmp = X; UPDATE table SET X = Y; UPDATE table SET Y = tmp; ALTER TABLE table DROP COLUMN tmp; ¿Algo como esto?

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

mysql> select * from test; +------+------+ | x | y | +------+------+ | 1 | 2 | | 3 | 4 | +------+------+ 2 rows in set (0.00 sec)

mysql> update test set x=y, y=x; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from test; +------+------+ | x | y | +------+------+ | 2 | 2 | | 4 | 4 | +------+------+ 2 rows in set (0.00 sec)

Answer 15

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;