Échanger les valeurs des colonnes dans MySQL

Question

J'ai une table MySQL avec des coordonnées, les noms des colonnes sont X et Y. Maintenant, je veux échanger les valeurs des colonnes dans cette table, de sorte que X devienne Y et Y devienne X. La solution la plus évidente serait de renommer les colonnes, mais je ne veux pas faire des changements de structure puisque je n'ai pas nécessairement les autorisations pour le faire.

Est-il possible de le faire avec UPDATE d'une manière ou d'une autre ? UPDATE table SET X=Y, Y=X évidemment ne fera pas ce que je veux.

---

Edit : Veuillez noter que ma restriction sur les permissions, mentionnée ci-dessus, empêche effectivement l'utilisation de ALTER TABLE ou d'autres commandes qui modifient la structure de la table/base de données. Renommer des colonnes ou en ajouter de nouvelles ne sont malheureusement pas des options.

Answer 1

Je viens d'avoir affaire à la même chose et je vais résumer mes conclusions.

1. El UPDATE table SET X=Y, Y=X ne fonctionne évidemment pas, car les deux valeurs seront simplement définies sur Y.

2. Voici une méthode qui utilise une variable temporaire. Merci à Antony dans les commentaires de http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ pour la modification de "IS NOT NULL". Sans cela, la requête fonctionne de manière imprévisible. Voir le schéma de la table à la fin de l'article. Cette méthode ne permute pas les valeurs si l'une d'entre elles est NULL. Utilisez la méthode #3 qui n'a pas cette limitation.

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

3. Cette méthode a été proposée par Dipin dans, encore une fois, les commentaires de http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ . Je pense que c'est la solution la plus élégante et la plus propre. Elle fonctionne avec les valeurs NULL et non NULL.

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

4. Une autre approche que j'ai trouvée et qui semble fonctionner :

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

Essentiellement, la première table est celle qui est mise à jour et la deuxième est utilisée pour extraire les anciennes données.
Notez que cette approche nécessite la présence d'une clé primaire.

Voici mon schéma de test :

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 2

Vous pouvez prendre la somme et soustraire la valeur opposée en utilisant X et Y.

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

Voici un exemple de test (et il fonctionne avec des nombres négatifs)

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>

Voici l'échange effectué

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>

Essayez-le ! !!

Answer 3

Le code suivant fonctionne pour tous les scénarios dans mes tests rapides :

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

Answer 4

UPDATE table SET X=Y, Y=X fera précisément ce que vous voulez (edit : dans PostgreSQL, pas MySQL, voir ci-dessous). Les valeurs sont extraites de l'ancienne ligne et affectées à une nouvelle copie de la même ligne, puis l'ancienne ligne est remplacée. Vous n'avez pas besoin d'utiliser une table temporaire, une colonne temporaire ou d'autres astuces d'échange.

@D4V360 : Je vois. C'est choquant et inattendu. J'utilise PostgreSQL et ma réponse y fonctionne correctement (je l'ai essayé). Voir le Docs PostgreSQL UPDATE (sous Paramètres, expression), où il est mentionné que les expressions à droite des clauses SET utilisent explicitement les anciennes valeurs des colonnes. Je vois que la clause correspondante Documentation sur l'UPDATE de MySQL contient la déclaration "Les affectations UPDATE à table unique sont généralement évaluées de gauche à droite" qui implique le comportement que vous décrivez.

Bon à savoir.

Answer 5

Ok, donc juste pour le fun, tu pourrais faire ça ! (en supposant que vous échangez les valeurs des chaînes)

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 petit jeu amusant qui abuse du processus d'évaluation de gauche à droite dans MySQL.

Sinon, utilisez simplement XOR si ce sont des nombres. Vous avez mentionné des coordonnées, alors avez-vous de belles valeurs entières, ou des chaînes complexes ?

Edit : Le truc XOR fonctionne comme ça d'ailleurs :

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