mySQL delete w/ join or subquery

Question

I have a select query which works:

SELECT    actions.id
FROM      actions
LEFT JOIN users
ON        users.id = actions.user_id
WHERE     users.id is null;

And I wanted to delete these records. I tried changing the select line to "DELETE" which produced an error. I also tried a subquery:

DELETE FROM actions WHERE id IN (
    SELECT    actions.id
    FROM      actions
    LEFT JOIN users
    ON        users.id = actions.user_id
    WHERE     users.id is null
);

Both attempts cause errors. While I've already solved the problem via PHP, it seems like this ought to be something the database can do with a single command. Is there a way?

The query is basically selecting all the rows in actions that do not have a corresponding entry in users ( the tables in question are now using foreign keys so this issue won't happen again ).

Answer 1

Try this:

DELETE    actions
FROM     actions
LEFT JOIN users
ON        users.id = actions.user_id
WHERE     users.id is null;

More info at http://dev.mysql.com/doc/refman/5.0/en/delete.html

Answer 2

MySQL doesn't like it if you try to DELETE/UPDATE rows and SELECT them in the same query. To solve this, they support a multi-table DELETE syntax.

DELETE a FROM actions a
LEFT OUTER JOIN users u ON u.id = a.user_id
WHERE u.id IS NULL;