Запрос delete не выполняется, хотя тот же запрос select-а выполняется можете помочь почему?

Question

[alpa_kz]

есть таблица mdate, где есть 3 столбца user_id, message_id, message_date, и мне надо удалить все сообщения, кроме самих последних от каждого юзера.

select * from mdate m1 
LEFT JOIN (
SELECT _user_id, max(message_date) as 'max_message_date'
 FROM mdate GROUP BY _user_id ) m2 
ON m1.message_date = m2.max_message_date AND 
m1._user_id = m2._user_id
WHERE m2._user_id is null;

этот запрос выводить все записи которые не последние, т.е. старые , если сделаю с inner join и без where ,
тогда выводить все последнее сообщения, а при delete говорить что синтаксическая ошибка? и понят не могу где ошибка?

delete from mdate m1 
LEFT JOIN (SELECT _user_id, max(message_date) as 'max_message_date' 
FROM mdate GROUP BY _user_id ) m2 ON m1.message_date = m2.max_message_date 
AND m1._user_id=m2._user_id
 WHERE m2._user_id is null;

пробовал и другой вариант
вот запрос

delete from mdate m0 where m0.message_id not in 
(select m1.message_id from mdate m1  
JOIN ( SELECT mg._user_id, max(mg.message_date) as 'max_message_date'  
FROM mdate as mg GROUP BY mg._user_id ) m2 
ON m1.message_date = m2.max_message_date 
AND m1._user_id = m2._user_id);

тоже не работает?!
система Debian, версия mysql или mariadb как вам убодно: 10.1.26-MariaDB-0+deb9u1

Answer 1

[Rsa97]

Так в последнем запросе уберите псевдоним m0, он там совсем ни к чему.

Comments

[Андрей]

вроде delete не работает, если вложен селект из той же таблицы, или я что-то путаю?

[alpa_kz]

Андрей, Спасибо! да, попробовав на английском гуглит, нашел на stackoverflow такой же ответ, но в документации про это нечего не написано или я что-то пропустил?

[alpa_kz]

alpa_kz, из-за этого сомневаюсь до сих пор(

[Андрей]

alpa_kz,

на вот такое:

delete from aaa where id in (select id from aaa where b_id=123456)

mariadb выдает

Ошибка SQL (1093): Table 'aaa' is specified twice, both as a target for 'DELETE' and as a separate source for data

mysql ведет себя ровно также, разве что текст выдает другой, может быть.

[Rsa97]

Андрей, Так вы хоть ошибку то прочитайте. Там же всё английским по белому написано.

[alpa_kz]

Rsa97, понятно

Answer 2

[Руслан .]

Можно вот так попробовать:

DELETE mdate
   FROM mdate aft
  WHERE EXISTS(SELECT 1
                 FROM mdate bfr
                WHERE bfr._user_id     = aft._user_id
                  AND bfr.message_date > aft.message_date)

Comments

[alpa_kz]

вот такая ошибка? ERROR 1109 (42S02): Unknown table 'mdate' in MULTI DELETE

[Руслан .]

alpa_kz, В MSSQL при такой ошибке срабатывал такой трюк:

DELETE mdate
   FROM mdate mdate
  WHERE EXISTS(SELECT 1
                 FROM mdate bfr
                WHERE bfr._user_id     = mdate._user_id
                  AND bfr.message_date > mdate.message_date)