Изменение значений столбцов в MySQL - программирование
Подтвердить что ты не робот

Изменение значений столбцов в MySQL

У меня есть таблица MySQL с координатами, имена столбцов - X и Y. Теперь я хочу поменять значения столбцов в этой таблице, так что X становится Y и Y становится X. Наиболее очевидным решением будет переименование столбцов, но я не хочу создавать изменения структуры, так как у меня нет разрешений на это.

Возможно ли это сделать с UPDATE? Таблица UPDATE SET X = Y, Y = X, очевидно, не будет делать то, что я хочу.

Изменить: обратите внимание, что мое ограничение на разрешения, упомянутое выше, эффективно предотвращает использование ALTER TABLE или других команд, которые изменяют структуру таблицы/базы данных. К сожалению, переименование столбцов или добавление новых не являются параметрами.

4b9b3361

ОТВЕТЫ

Ответ 1

Мне просто пришлось иметь дело с тем же, и я обобщу свои выводы.

  • Подход UPDATE table SET X=Y, Y=X явно не работает, поскольку он просто устанавливает оба значения в Y.

  • Здесь используется метод, который использует временную переменную. Спасибо Антонию из комментариев http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ для настройки "IS NOT NULL". Без него запрос работает непредсказуемо. См. Схему таблицы в конце сообщения. Этот метод не меняет значения, если один из них равен NULL. Используйте метод №3, который не имеет этого ограничения.

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

  • Этот метод был предложен Dipin in, но опять же, комментарии http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/. Я думаю, что это самое элегантное и чистое решение. Он работает как с значениями NULL, так и с не-NULL.

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

  • Другой подход, с которым я столкнулся, кажется, работает:

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

По существу, первая таблица обновляется, а вторая используется для извлечения старых данных.
Обратите внимание, что для этого подхода требуется наличие первичного ключа.

Это моя тестовая схема:

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

Ответ 2

Вы можете взять сумму и вычесть противоположное значение с помощью X и Y

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

Вот пример теста (и он работает с отрицательными номерами)

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>

Здесь выполняется своп

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>

Дайте ему попробовать!!!

Ответ 3

Следующий код работает для всех сценариев моего быстрого тестирования:

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

Ответ 4

Таблица обновлений SET X = Y, Y = X будет делать именно то, что вы хотите (редактировать: в PostgreSQL, а не в MySQL, см. Ниже). Значения берутся из старой строки и присваиваются новой копии той же строки, затем старая строка заменяется. Вам не нужно прибегать к использованию временной таблицы, временного столбца или других хитростей подкачки.

@D4V360: Понятно. Это шокирует и неожиданно. Я использую PostgreSQL, и мой ответ там работает правильно (я пробовал). См. Документацию PostgreSQL UPDATE (в разделе Параметры, выражение), где упоминается, что выражения в правой части предложений SET явно используют старые значения столбцов. Я вижу, что соответствующие документы MySQL UPDATE содержат оператор "Назначения UPDATE для одной таблицы обычно оцениваются слева направо", что подразумевает описанное вами поведение.

Хорошо знать.

Ответ 5

Хорошо, просто ради удовольствия, вы могли бы это сделать! (предполагая, что вы меняете строковые значения)

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)

Хорошее удовольствие от использования процесса оценки слева направо в MySQL.

В качестве альтернативы просто используйте XOR, если они являются числами. Вы упомянули координаты, у вас есть прекрасные целые значения или сложные строки?

Изменить: материал XOR работает так:

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

Ответ 6

Две альтернативы  1. Используйте временную таблицу  2. Расследовать   алгоритм XOR

Ответ 7


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;
Что-то вроде этого?

Редактировать: О Greg комментарий: Нет, это не работает:


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)

Ответ 8

Я считаю, что промежуточная переменная обмена является лучшей практикой таким образом:

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

Во-первых, он работает всегда; во-вторых, он работает независимо от типа данных.

Несмотря на Both

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

и

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

работают обычно, только для типа числовых данных, и вы несете ответственность за предотвращение переполнения, вы не можете использовать XOR между подписанным и unsigned, вы также не можете использовать сумму для возможности переполнения.

и

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

не работает если c1 - 0 или NULL или строка с нулевой длиной или просто пробелы.

Нам нужно изменить его на

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

Вот сценарии:

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)

Ответ 9

Это, безусловно, работает! Я просто нуждался в нем, чтобы обменять цены на Евро и SKK.:)

UPDATE tbl SET X=Y, [email protected] where @temp:=X;

Вышеуказанное не будет работать (ERROR 1064 (42000): у вас есть ошибка в синтаксисе SQL)

Ответ 10

Предполагая, что вы подписали целые числа в своих столбцах, вам может потребоваться использовать CAST (a ^ b AS SIGNED), так как результатом оператора ^ является неподписанное 64-разрядное целое число в MySQL.

В случае, если это помогает кому-либо, здесь метод, который я использовал для обмена одним и тем же столбцом между двумя заданными строками:

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

где $1 и $2 являются ключами двух строк, а $3 - результатом первого запроса.

Ответ 11

Я не пробовал, но

UPDATE tbl SET @temp=X, X=Y, [email protected]

Можете сделать это.

Марк

Ответ 12

Вы можете изменить имена столбцов, но это скорее хак. Но будьте осторожны с любыми индексами, которые могут быть в этих столбцах

Ответ 13

Обмен значениями столбцов с использованием одного запроса

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

веселит...

Ответ 14

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;

Ответ 15

Мне нужно было просто переместить значение из одного столбца в другой (например, архивирование) и reset значение исходного столбца.
Ниже приведена ссылка (ссылка № 3 из принятого ответа выше).

Update MyTable set X= (@temp:= X), X = 0, Y = @temp WHERE ID= 999;

Ответ 16

В этом примере поменялись местами start_date и end_date для записей, в которых даты неверны (при выполнении ETL в основную переписку я обнаружил некоторые даты начала позже, чем даты их окончания. Вниз, плохие программисты!)

На месте я использую MEDIUMINT по соображениям производительности (например, юлианские дни, но с корнем 0 в 1900-01-01), поэтому я был в порядке, выполняя условие WHERE mdu.start_date> mdu.end_date.

PK были на всех 3 столбцах индивидуально (по причинам эксплуатации/индексации).

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;

Ответ 17

Название таблицы - клиент. поля a и b, поменяйте местами значение на b ;.

ОБНОВЛЕНИЕ клиента УСТАНОВИТЬ a = (@temp: = a), a = b, b = @temp

Я проверил это работает нормально.

Ответ 18

В SQL Server вы можете использовать этот запрос:

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

Ответ 19

Допустим, вы хотите поменять значение имени и фамилии в tb_user.

Самым безопасным будет:

  1. Скопируйте tb_user. Таким образом, у вас будет 2 таблицы: tb_user и tb_user_copy
  2. Используйте запрос 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