Почему MySQL не может обновить таблицу и выбрать из той же таблицы в подзапросе
В нижней части MySQL v5.6 страница документации для команды UPDATE написано:
You cannot update a table and select from the same table in a subquery.
Например, это не удастся:
UPDATE actor
SET last_name = 'foo'
WHERE actor_id IN (
SELECT actor_id
FROM actor
WHERE last_name = 'bar');
Примечание. Предположим, что столбец last_name не является частью индекса.
Почему это не разрешено и есть ли стабильный обходной путь?
P.S. - Спасибо за обходной путь! Не могли бы вы также объяснить, почему приведенный выше пример не разрешен?
Ответы 2
Это простой трюк:
UPDATE actor
SET last_name = 'foo'
WHERE last_name = 'bar'
Что бы вы ни делали, это то же самое, что и приведенный выше запрос.
Объяснение: Вы не можете просто обновить то, что выбрали.
Если вы хотите продолжить свой поток, дайте псевдоним для подзапроса, используя временную таблицу,
UPDATE actor a1
SET a1.last_name = 'foo'
WHERE a1.actor_id IN (
SELECT a2.actor_id
FROM actor a2
WHERE a2.last_name = 'bar');
ИЛИ
UPDATE actor a1 SET a1.last_name='foo'
WHERE a1.actor_id IN (SELECT a2.actor_id
from (SELECT * FROM actor) a2
WHERE a2.last_name = 'bar');
Другими словами, в MySQL вы не можете изменить ту же таблицу, которую вы используете в части SELECT. Это поведение задокументировано здесь.
И в stackoverflow ссылка1 и ссылка2.
Note:This is because your update could be cyclical… what if updating that record causes something to happen which made the WHERE condition FALSE? You know that isn’t the case, but the engine doesn’t. There also could be opposing locks on the table in the operation.
Вы пытались избежать подзапроса?
UPDATE actor
SET last_name = 'foo'
WHERE last_name = 'bar';
Слишком много где ).
Это работает, и я удивлен - не должно ли использование столбца, отличного от PK, в предложении WHERE запускать безопасный режим обновления MySQL для блокировки этой команды?
Итак, причина, по которой ваш оператор SQL работает, заключается в том, что в моей БД (пример БД Sakila) есть индекс (ключ) в столбце last_name. Так что это не стабильный обходной путь, к сожалению.
Спасибо за Ваш ответ. Не могли бы вы объяснить, почему я не могу обновить то, что выбираю.