The delete query is not executed, although the same select query is being executed, can you help why?

Question

MariaDB

there is an mdate table, where there are 3 columns user_id, message_id, message_date, and I need to delete all messages except the last ones from each user.

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;

this query will display all records that are not the latest, i.e. old ones, if I do it with inner join and without where ,
then display all the latest messages, and when delete say that there is a syntax error? and I can not understand where is the error?

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;

tried another option
here is the query

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);

doesn't work either?
Debian system, mysql or mariadb version as you like: 10.1.26-MariaDB-0+deb9u1

Answer 1

So in the last request, remove the m0 alias, it is completely useless there.

Answer 2

You can try like this:

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)