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-30T18:46:32.157

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 '15 at 18:46

answered Apr 30 '15 at 18:43

Abhik Chakraborty

44,654
6
52
63

No yeah, I need to delete all tables rows that match the session table row selected – Nicolas Racine Apr 30 '15 at 18:45
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.. – Nicolas Racine Apr 30 '15 at 18:48
An OUTER JOIN in a DELETE clause is a rare event. Maybe there is a reason! – Strawberry Apr 30 '15 at 19:09
@Strawberry Why are you saying that ? What is the reason ? – Nicolas Racine Apr 30 '15 at 19:17

Delete rows from multiple tables with a left join

1 Answers1