Поменять местами значения двух строк в MySQL без нарушения уникального ограничения?

Question

Поменять местами значения двух строк в MySQL без нарушения уникального ограничения?

Я пробовал этот код

UPDATE testing_table t1
INNER JOIN testing_table t2 ON (t1.id, t2.id) IN ((1, 2),(2, 1))
SET t1.emp_id = t2.emp_id

но, похоже, есть эта ошибка

# 1062 — Дублирующая запись ‘3’ для ключа ’emp_id’

Дайте мне знать, как поменять 2 столбца без нарушения уникальных ограничений в MySQL

4

mysql php swap unique-constraint

Решение

Другие решения

Пожалуйста, попробуйте с этим. Это должно работать для вас 🙂

START TRANSACTION;
UPDATE testing_table
SET emp_id =
CASE
WHEN emp_id = 1 THEN -2
WHEN emp_id = 2 THEN -1
END
WHERE emp_id IN (1,2);

UPDATE testing_table
SET emp_id = - emp_id
WHERE emp_id IN (-1,-2);
COMMIT;

0

Источник

Accepted Answer

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

Поэтому для того, чтобы значения столбца можно было поменять местами, необходимо разрешить столбцу получать временное значение, которое не будет конфликтовать с какими-либо ограничениями. Так что если у вас есть столбец, который имеет ограничение внешнего ключа, а также не позволяет null значения также будет трудно найти такое значение во всех возможных случаях.

Для следующего решения null значения должны быть разрешены. Если они в настоящее время не, то сначала выполните это заявление, чтобы null для emp_id колонка:

alter table testing_table modify column emp_id int null;

Затем:

start transaction;
update testing_table
set emp_id = null
where  id = 1 and if(@emp1 := emp_id, 1, 1)
or  id = 2 and if(@emp2 := emp_id, 1, 1);

update testing_table
set emp_id = if(id = 1, @emp2, @emp1)
where  id in (1, 2);
commit;

SQL скрипка.

объяснение

Сначала будут установлены значения null, сохраняя свои предыдущие значения в @emp1 а также @emp2и обновляет те же записи со значениями, которые хранятся в этих переменных, но меняются местами.

ifв первом update оператор только там, чтобы убедиться, что назначение возвращает true выражение и сделать where условие выполнено Отметим также, что оценка короткого замыкания будет гарантировать, что эти назначения происходят только тогда, когда левая сторона and Оператор оценивается в true,

2