Zamiana wartości kolumn w MySQL

Mam tabelę MySQL ze współrzędnymi, nazwy kolumn to X i Y. Teraz chcę zamienić wartości kolumn w tej tabeli, tak aby X stało się Y, a Y stało się X. Najbardziej oczywistym rozwiązaniem byłaby zmiana nazw kolumn, ale ja nie chcę wprowadzać zmian w strukturze, ponieważ niekoniecznie mam do tego uprawnienia.

Czy można to w jakiś sposób zrobić z UPDATE ? UPDATE table SET X = Y, Y = X oczywiście nie zrobi tego, co chcę.

Edycja: Należy pamiętać, że wspomniane powyżej moje ograniczenie uprawnień skutecznie uniemożliwia użycie ALTER TABLE lub innych poleceń zmieniających strukturę tabeli / bazy danych. Zmiana nazw kolumn lub dodanie nowych nie jest niestety opcją.

Po prostu musiałem poradzić sobie z tym samym i podsumuję moje ustalenia.

1. To UPDATE table SET X=Y, Y=Xpodejście oczywiście nie działa, ponieważ ustawi obie wartości na Y.

2. Oto metoda wykorzystująca zmienną tymczasową. Podziękowania dla Antony'ego za komentarze http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ za poprawkę „NIE JEST NULL”. Bez tego zapytanie działa nieprzewidywalnie. Zobacz schemat tabeli na końcu postu. Ta metoda nie zamienia wartości, jeśli jedna z nich ma wartość NULL. Użyj metody nr 3, która nie ma tego ograniczenia.

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

3. Ta metoda została zaproponowana przez Dipina w komentarzach http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ . Myślę, że to najbardziej eleganckie i czyste rozwiązanie. Działa zarówno z wartościami NULL, jak i innymi niż NULL.

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

4. Inne podejście, które wymyśliłem, wydaje się działać:

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

Zasadniczo pierwsza tabela jest aktualizowana, a druga służy do pobierania starych danych.
Należy pamiętać, że to podejście wymaga obecności klucza podstawowego.

Oto mój schemat testu:

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

Możesz wziąć sumę i odjąć przeciwną wartość za pomocą X i Y

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

Oto przykładowy test (działa z liczbami ujemnymi)

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>

Oto wykonywana zamiana

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>

Spróbuj !!!

Poniższy kod działa dla wszystkich scenariuszy w moim szybkim testowaniu:

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

UPDATE table SET X = Y, Y = X zrobi dokładnie to, co chcesz (edycja: w PostgreSQL, nie w MySQL, patrz poniżej). Wartości są pobierane ze starego wiersza i przypisywane do nowej kopii tego samego wiersza, a następnie zastępowany jest stary wiersz. Nie musisz uciekać się do korzystania z tymczasowej tabeli, tymczasowej kolumny ani innych sztuczek wymiany.

@ D4V360: Rozumiem. To szokujące i nieoczekiwane. Używam PostgreSQL i moja odpowiedź tam działa poprawnie (próbowałem). Zobacz dokumentację PostgreSQL UPDATE (w części Parametry, wyrażenie), gdzie wspomniano, że wyrażenia po prawej stronie klauzul SET jawnie używają starych wartości kolumn. Widzę, że odpowiednie dokumenty MySQL UPDATE zawierają stwierdzenie „Przypisania aktualizacji w pojedynczej tabeli są generalnie oceniane od lewej do prawej”, co sugeruje zachowanie, które opisujesz.

Dobrze wiedzieć.

Ok, więc dla zabawy możesz to zrobić! (zakładając, że zamieniasz wartości ciągów)

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)

Niezła zabawa nadużywając procesu oceny od lewej do prawej w MySQL.

Alternatywnie, po prostu użyj XOR, jeśli są to liczby. Wspomniałeś o współrzędnych, więc czy masz urocze wartości całkowite lub złożone ciągi?

Edycja: rzeczy XOR działają tak przy okazji:

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

Uważam, że posiadanie pośredniej zmiennej wymiany jest najlepszą praktyką w taki sposób:

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

Po pierwsze, działa zawsze; po drugie, działa niezależnie od typu danych.

Pomimo obu

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

i

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

działają zwykle, nawiasem mówiąc, tylko dla typu danych liczbowych, a Twoim obowiązkiem jest zapobieganie przepełnieniu, nie możesz używać XOR między podpisanymi a niepodpisanymi, nie możesz również użyć sumy do przepełnienia.

I

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

nie działa, jeśli c1 jest równe 0 lub NULL, ciąg znaków o zerowej długości lub tylko spacje.

Musimy to zmienić na

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

Oto skrypty:

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)

Dwie alternatywy 1. Użyj tabeli tymczasowej 2. Zbadaj algorytm XOR

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;

Edycja: O komentarzu Grega: Nie, to nie działa:

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)

To na pewno działa! Potrzebowałem go tylko do zamiany kolumn cen w Euro i SKK. :)

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

Powyższe nie zadziała (ERROR 1064 (42000): masz błąd w składni SQL)

Zakładając, że w kolumnach znajdują się liczby całkowite ze znakiem, może być konieczne użycie funkcji CAST (a ^ b AS SIGNED), ponieważ wynikiem operatora ^ jest 64-bitowa liczba całkowita bez znaku w MySQL.

Na wypadek, gdyby to komuś pomogło, oto metoda, której użyłem do zamiany tej samej kolumny między dwoma podanymi wierszami:

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

gdzie $ 1 i $ 2 to klucze dwóch wierszy, a $ 3 to wynik pierwszego zapytania.

Nie próbowałem tego, ale

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

Może to zrobić.

znak

Państwo mogłoby zmienić nazwy kolumn, ale to jest bardziej hack. Uważaj jednak na indeksy, które mogą znajdować się w tych kolumnach

Nazwa tabeli to customer. pola to a i b, zamień wartość na b ;.

AKTUALIZUJ SET klienta a = (@ temp: = a), a = b, b = @temp

Sprawdziłem, że to działa dobrze.

W SQL Server możesz użyć tego zapytania:

update swaptable 
set col1 = t2.col2,
col2 = t2.col1
from swaptable t2
where id = t2.id

Zamiana wartości kolumn za pomocą pojedynczego zapytania

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

Twoje zdrowie...!

Musiałem po prostu przenieść wartość z jednej kolumny do drugiej (jak archiwizacja) i zresetować wartość oryginalnej kolumny.
Poniższe (odniesienie nr 3 z zaakceptowanej odpowiedzi powyżej) zadziałało dla mnie.

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;

Ten przykład swapy START_DATE i END_DATE dla rekordów, gdzie terminy są w niewłaściwy sposób okrągłe (przy wykonywaniu ETL do poważnej przepisać, znalazłem pewne początkowe daty później niż ich koniec daty . Dół, źli programiści!).

Na miejscu używam MEDIUMINT ze względu na wydajność (jak dni juliańskie, ale mając 0 root z 1900-01-01), więc byłem OK, wykonując warunek GDZIE mdu.start_date> mdu.end_date .

PK znajdowały się na wszystkich 3 kolumnach indywidualnie (ze względów operacyjnych / indeksacyjnych).

UPDATE monitor_date mdu
INNER JOIN monitor_date mdc
    ON mdu.register_id = mdc.register_id
    AND mdu.start_date = mdc.start_date
    AND mdu.end_date = mdc.end_date
SET mdu.start_date = mdu.end_date, mdu.end_date = mdc.start_date
WHERE mdu.start_date > mdu.end_date;

Powiedzmy, że chcesz zamienić wartość imienia i nazwiska w tb_user.

Najbezpieczniejsze byłoby:

1. Skopiuj tb_user. Będziesz więc mieć 2 tabele: tb_user i tb_user_copy
2. Użyj zapytania UPDATE INNER JOIN

UPDATE tb_user a
INNER JOIN tb_user_copy b
ON a.id = b.id
SET a.first_name = b.last_name, a.last_name = b.first_name

Możesz złożyć poniższe zapytanie, dla mnie zadziałało idealnie.

Table name: studentname
only single column available: name


update studentnames 
set names = case names 
when "Tanu" then "dipan"
when "dipan" then "Tanu"
end;

or

update studentnames 
set names = case names 
when "Tanu" then "dipan"
else "Tanu"
end;