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 ).

Eljakim · Accepted Answer · 2011-11-18 16:52:11Z

5

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

answered Nov 18, 2011 at 16:52

Eljakim

6,9372 gold badges18 silver badges17 bronze badges

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

Comments

Bill Karwin · Accepted Answer · 2011-11-18 16:52:17Z

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;

answered Nov 18, 2011 at 16:52

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

2 Comments

Will Over a year ago

Thanks. By the way - recently bought your book. Good stuff.

Bill Karwin Over a year ago

Thanks! I'm glad that you enjoyed my book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming. :-)

Shakti Singh · Accepted Answer · 2011-11-18 16:53:10Z

1

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

answered Nov 18, 2011 at 16:53

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

Collectives™ on Stack Overflow

mySQL delete w/ join or subquery

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related