Delete rows from multiple tables with a left join

Question

I searched a lot online and I can't figure out how do to do my simple task.

I want to delete all those row that im selecting with this following query.

`SELECT * FROM `session` s
LEFT JOIN `pages` p ON (s.Id = p.VisitorSessionId)
LEFT JOIN `product_views`ps ON (s.Id = ps.VisitorSessionId)
WHERE s.`JSEnabled` = 0

And I tried to delete those row by using

DELETE FROM `session` s
LEFT JOIN `pages` p ON (s.Id = p.VisitorSessionId)
LEFT JOIN `product_views`ps ON (s.Id = ps.VisitorSessionId)
WHERE s.`JSEnabled` = 0

I tried to do something similar to this post answer Deleting rows with MySQL LEFT JOIN But its not working.

How can i make this query without having to do a loop in my application ?

Abhik Chakraborty · Accepted Answer · 2015-04-30 18:46:32Z

4

If you need to delete only from one table then it could be done as

DELETE s FROM `session` s
LEFT JOIN `pages` p ON (s.Id = p.VisitorSessionId)
LEFT JOIN `product_views`ps ON (s.Id = ps.VisitorSessionId)
WHERE s.`JSEnabled` = 0

For deleting from multiple tables with join it would be as

DELETE s,p,ps FROM `session` s
LEFT JOIN `pages` p ON (s.Id = p.VisitorSessionId)
LEFT JOIN `product_views`ps ON (s.Id = ps.VisitorSessionId)
WHERE s.`JSEnabled` = 0

edited Apr 30, 2015 at 18:46

answered Apr 30, 2015 at 18:43

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

4 Comments

Nicolas Racine Over a year ago

No yeah, I need to delete all tables rows that match the session table row selected

Nicolas Racine Over a year ago

Oh wow,.. I had it then, But i was using tables names instead,. Well thanks for the fast answer! I have to wait to mark this answer as accepted lol..

Strawberry Over a year ago

An OUTER JOIN in a DELETE clause is a rare event. Maybe there is a reason!

Nicolas Racine Over a year ago

@Strawberry Why are you saying that ? What is the reason ?

Collectives™ on Stack Overflow

Delete rows from multiple tables with a left join

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related