SELECT внутри запроса DELETE одной таблицы. Как правильно?

Question

[Дмитрий]

Делал запрос типа этого:

DELETE FROM `table` WHERE (SELECT COUNT(*) FROM `table` WHERE `idu` = 1) > 5 AND `idu` = 1 LIMIT 3

Выдает ошибку "#1093 - Не допускается указание таблицы 'table' в списке таблиц FROM для внесения в нее изменений"
Как правильно?
Вообще нужно оставить три записи с одним идентификатором "idu", остальные старые удалить.

Comments

[Антон Р.]

Мне кажется после WHERE у тебя должно быть указание некого поля а у тебя там идет расчет какого-то числового показателя в результате получается DELETE FROM `table` WHERE Какая-то-Цифра > 5... Думаю в этом дело.

[Дмитрий]

Вот и я не знаю, как указать условие в запросе, если записей с одним idu больше 3, тогда мы удаляем все записи кроме 3 новых записей.

[Антон Р.]

inilim, получить всё количество и удалить всё-минус-три.

[Дмитрий]

Антон Р., думал сделать одним запросом.

[Антон Р.]

inilim, так и делай, только после LIMIT делай вычисление Все-минус-три.

[Дмитрий]

Антон Р., Как сделать "Все"? select count(*) после limit? выдает ошибку.

Answer 1

[Иван Мельников]

Для MySQL 5.7 и MySQL 8.0:
Запрос

DELETE FROM `table` WHERE `idu` = 1 AND `time_end` NOT IN
(SELECT `time_end` FROM `table` ORDER BY `time_end` DESC LIMIT 3)

удалит из таблицы все записи с `idu` = 1 кроме трех последних.
Для MySQL 5.6 и более ранних:

DELETE FROM `table` WHERE `idu` = 1 AND `time_end` <
(SELECT * FROM (SELECT `time_end` FROM `table` ORDER BY `time_end` DESC LIMIT 3) as T
ORDER BY `time_end` LIMIT 1)

Comments

[Дмитрий]

#1235 - Эта версия MySQL пока еще не поддерживает 'LIMIT & IN/ALL/ANY/SOME subquery'
MySQL 5.6

[Иван Мельников]

inilim, какая эта версия?
MySQL 8 и MySQL 5.7 всё это поддерживают.

[Иван Мельников]

inilim, вариант для 5.6 добавил в ответ.

[Дмитрий]

Иван Мельников, поставил новый openserver, с MySQL 8, первый способ все также не работает, ошибка говорит данная версия не поддерживает. А второй способ работает, но долго :(

[Иван Мельников]

inilim, странно, у меня всё работает.

[Иван Мельников]

inilim, кстати, а индекс на поле time_end у вас имеется?

[Дмитрий]

Иван Мельников, нет, побегал по stackexchange и нашел

DELETE p FROM table AS p JOIN ( SELECT id FROM table WHERE idu = 1 ORDER BY time_end DESC LIMIT 999 OFFSET 3 ) AS lim ON p.id in (lim.id)