Используйте CTE для обновления или удаления в MySQL

Новая версия MySQL, 8.0, теперь поддерживает общие табличные выражения.

Согласно инструкции:

Предложение WITH разрешается в начале инструкций SELECT, UPDATE и DELETE: 

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

Итак, подумал я, приведем следующую таблицу: 

ID lastName firstName
----------------------
1  Smith    Pat
2  Smith    Pat
3  Smith    Bob

Я могу использовать следующий запрос: 

;WITH ToDelete AS 
(
   SELECT ID,
          ROW_NUMBER() OVER (PARTITION BY lastName, firstName ORDER BY ID) AS rn
   FROM mytable
)   
DELETE FROM ToDelete

Чтобы удалить дубликаты из таблицы, как я мог бы сделать в SQL Сервер.

Оказывается я ошибался. Когда я пытаюсь выполнить DELETE stament из MySQL Workbench, я получаю ошибку:

Код Ошибки: 1146. Таблица "todelete" не существует

Я также получаю сообщение об ошибке, когда пытаюсь сделать UPDATE с помощью CTE.

Итак, мой вопрос заключается в том, как можно использовать предложение WITH в контексте оператора UPDATE или DELETE в MySQL (как указано в руководстве версии 8.0)?
2 2
mysql sql-delete sql-update common-table-expression mysql-8.0

2 ответа:

Это похоже на опубликованную ошибку в MySQL 8.x. Из этого отчета об ошибке:

В версии стандарта SQL 2015 года CTE не может быть определен в UPDATE; MySQL позволяет это, но делает CTE доступным только для чтения (сейчас мы обновляем документацию, чтобы упомянуть об этом). Тем не менее, можно было бы использовать представление вместо CTE; тогда представление может быть обновляемым, но из-за наличия оконных функций оно материализуется во временную таблицу (она не объединяется), поэтому не обновляется (мы также упомянем об этом в доке).

Все вышесказанное относится и к удалению.

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

2

Поскольку CTE не подлежит обновлению, для удаления строк необходимо обратиться к исходной таблице. Я думаю, что вы ищете что-то вроде этого: 

WITH ToDelete AS 
(
   SELECT ID,
          ROW_NUMBER() OVER (PARTITION BY lastName, firstName ORDER BY ID) AS rn
   FROM mytable
)   
DELETE FROM mytable USING mytable JOIN ToDelete ON mytable.ID = ToDelete.ID
WHERE ToDelete.rn > 1;
1