Question 20518 in Mysql Question posted in
The official MySQL documentation can be found here.

Delete from a table with right join including a limit – Mysql

When I tried to delete from a table with right join is working, when I added a LIMIT query fails :

DELETE la 
FROM log_artim la
RIGHT JOIN corm_artim ca ON la.idCorm = ca.id 
WHERE la.da <= '2022-02-02 02:02:02'
LIMIT 10

If I remove limit is working fine, if not I have an error :

MySQL server version for the right syntax to use near 'LIMIT 10' at line 5

Have an idea about hits error ?

Tags:

3

Answers


  1. 0

    As per MYSQL Documentation

    You cannot use ‘ORDER BY’ or ‘LIMIT’ in a multiple-table DELETE.

    enter image description here

    Login or Signup to reply.
  2. 0

    If you want to use LIMIT clause, you should see below code;

    DELETE la 
FROM log_artim la
WHERE la.id in (
  SELECT la.id 
  FROM log_artim la
  RIGHT JOIN corm_artim ca ON la.idCorm = ca.id 
  WHERE la.da <= '2022-02-02 02:02:02'
  LIMIT 10
)
    Login or Signup to reply.
  3. 0

    The following query shall work for you using LIMIT clause

    delete from log_artim 
where idCorm in (
  select la.idCorm
  from log_artim la
  right join corm_artim ca on la.idCorm = ca.id 
  where la.da <= '2022-02-02 02:02:02'
  limit 10
);

    Update :

    To resolve the issue :
    This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’

    You can modify the above query as :

    delete log_artim
from log_artim
right join (
  select la.idCorm
  from log_artim la
  inner join corm_artim ca on la.idCorm = ca.id 
  where la.da <= '2022-02-02 02:02:02'
  limit 10
) t1 on log_artim.idCorm = t1.idCorm;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search