Search code examples
mysqlsubquerysql-delete

SQL syntax error for deleting multiple rows from a subquery

I have a relational MySQL database using InnoDB which connects courses to course attendees. The problem with this database is that the course_id column in course_attendees was not set as foreign key. There are alot of courses missing where the course_attendees table is trying to refer to.

I wanted to delete those records, since the courses don't belong anymore. I wrote this select query which selects all the courses that should be deleted:

SELECT
    ca.`id`
  FROM `course_attendees` AS ca
    LEFT JOIN `courses` c
      ON ca.`course_id` = c.`id`
  WHERE c.`id` IS NULL

Now, when I try to wrap this in a DELETE query with the use of a subquery like this:

DELETE FROM courses AS C1
WHERE C1.`id` IN (
  SELECT
    ca.`id`
  FROM `course_attendees` AS ca
    LEFT JOIN `courses` c
      ON ca.`course_id` = c.`id`
  WHERE c.`id` IS NULL
);

I get the following error:

[2018-08-30 08:34:26] [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS C1

[2018-08-30 08:34:26] [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS C1
[2018-08-30 08:34:26] WHERE C1.`id` IN (
[2018-08-30 08:34:26] SELECT
[2018-08-30 08:34:26] ca.`id`
[2018-08-30 08:34:26] FROM `course_attendees` AS c' at line 1

Since the SELECT query works, what is the problem here and how can I fix it?

EDIT

After Tim's answer, it got me into this error:

[HY000][1093] You can't specify target table 'courses' for update in FROM clause
Solution

  • Your outer delete query is not correlated to the subquery at all, so you don't logically need aliases:

    DELETE
FROM courses
WHERE id IN (
    SELECT id FROM
    (
        SELECT ca.id
        FROM course_attendees AS ca
        LEFT JOIN courses c
            ON ca.course_id = c.id
        WHERE c.id IS NULL
    ) t
);

    I'm not sure that aliases are allowed in a delete statement on just a single table. They are allowed for a delete join, but you're not doing that.