Видалення рядків із таблиці в MySql. Error Code: 1093, 1175

Маю значний досвід програмування в MS SQL Server 2000. Зараз вивчаю MySql. Опишу проблему, яка виникла під час видалення рядків із таблиці.

Є таблиця:

CREATE TABLE `menu` (

`MenuID` int(11) NOT NULL AUTO_INCREMENT,

`MenuIDP` int(11) NOT NULL,

`Menu` varchar(100) NOT NULL,

PRIMARY KEY (`MenuID`),

KEY `IX_MenuIDP` (`MenuIDP`),

) ENGINE=InnoDB AUTO_INCREMENT=407 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

В СУБД MS SQL Server вкладений запит виконувався без проблем:

delete from menu where `MenuIDP` not in(select `MenuID` from menu);

"Видали, будь ласка, всі рядки із таблиці menu, в яких значення поля `MenuIDP` не співпадають із жодним із значень поля `MenuID`". Погодьтеся, що інструкція лаконічна і зрозуміла.

MySql на такий запит чемно повернув

Error Code: 1093

You can't specify target table 'menu' for update in FROM clause

Вивчаючи досвід колег в Internet, дізнався, що MySql не може видалити рядки із таблиці, яка тимчасово блокується командою select. Очевидно в цьому є деякий смисл.

Если вам нужно чтобы заработал ваш запрос - могу сказать, что mysql можно перехитрить делая селект не из самой таблицы, а присоединяя ее к чему либо (например к select 1 as k) по right outer join.(Удаление данных из таблицы MySql)

Хитрити із MySql я не став, а скористався декількома командами із використанням тимчасової таблиці:

   1:  /* створити тимчасову таблицю */

   2:  create temporary table ForRowsDelete(`MenuID` int not null);

   3:  /* вставити код рядків, які потрібно видалити, у тимчасову таблицю */

   4:  insert into ForRowsDelete(`MenuID`)

   5:  select `MenuID` from menu where `MenuIDP` not in(select `MenuID` from menu);

   6:  /* видалити із таблиці menu */

   7:  delete from menu where `MenuID` in(select `MenuID` from ForRowsDelete);

   8:  /* видалити тимчасову таблицю */

   9:  drop temporary table ForRowsDelete;

Багато тексту, зате покроково.

Інструкція 7 чемно повернула:

Error Code: 1175

You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

Виявляється, що MySql просить, щоб після WHERE було вказано індексоване поле незалежно від того, чи потрібно воно для запиту. Але ж поле `MenuID` є PRIMARY?!

В інструкцію 7 додав після WHERE "and `MenuIDP`>0":

delete from menu where `MenuID` in(select `MenuID` from ForRowsDelete) and `MenuIDP`>0;

Такий підхід до видалення даних в MySql забезпечує насамперед високу швидкодію і безпеку інформації. Не сперечаюся.

Update 20.09.2010

Щодо Error Code: 1175. Як виявилося, консольний режим пропускав запити на видалення. Помилку видавала MySql WorkBench 5.2.27CE. Вивчаючи цю IDE, знайшов те, що потрібно.

Достатньо відключити у Edit -> Preferences ... -> SQL editor опцію Forbid UPDATE and DELETE statements without a WHERE clause (safe updates). Але за безпеку відповідати будете самостійно.