witoong623 witoong623 - 11 days ago 7
MySQL Question

MySQL delete statement with subquery in IN cluase and LIMIT

I tried to delete records which condition related to another table and I also want to limit records that will be deleted.

DELETE e.* FROM employees e
WHERE emp_no IN (
SELECT ee.emp_no
FROM (
SELECT e.emp_no
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no
GROUP BY e.emp_no
HAVING COUNT(s.emp_no) = 1)
ee)
AND emp_no NOT IN (
SELECT dmm.emp_no
FROM (
SELECT dm.emp_no
FROM dept_manager dm)
dmm)
LIMIT 8384


but I get an error


1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 8384' at line 17


I wonder is this because its condition, I have no idea what's wrong.

Answer

You are deleting from a single table, which for some reason MySQL doesn't support table alias' for:-

DELETE 
FROM employees 
WHERE emp_no IN (
    SELECT ee.emp_no
    FROM (
        SELECT e.emp_no
        FROM employees e, salaries s
        WHERE e.emp_no = s.emp_no
        GROUP BY e.emp_no
        HAVING COUNT(s.emp_no) = 1)
    ee) 
AND emp_no NOT IN (
    SELECT dmm.emp_no
    FROM (
        SELECT dm.emp_no
        FROM dept_manager dm)
    dmm)
LIMIT 8384
Comments