SQL syntax error for deleting multiple rows from a subquery

Question

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

Tim Biegeleisen · Accepted Answer · 2018-08-30 06:44:00Z

2

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.

edited Aug 30, 2018 at 6:44

answered Aug 30, 2018 at 6:40

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Bas Over a year ago

Thanks, I didn't know. It got me into this error though [HY000][1093] You can't specify target table 'courses' for update in FROM clause

Strawberry Over a year ago

This still seems like more query than is strictly necessary

Tim Biegeleisen Over a year ago

Wrap one extra time with a subquery to fix that problem.

Tim Biegeleisen Over a year ago

@Strawberry I give up trying to write an delete inner join, if you can then I'm happy to upvote you. It's more complicated than it looks.

Tim Biegeleisen Over a year ago

It worked because we fixed the alias problem, and then wrapped the subquery. This forces MySQL to materialize that set of course attendee id values, so the delete won't complain about it.

Collectives™ on Stack Overflow

SQL syntax error for deleting multiple rows from a subquery

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related