MYSQL Delete multiple or single row from different columns

Question

I am trying to delete a post I have created, but that post could also have likes or comments.

So obviously I want to be able to delete those comments or likes if I try to delete the post!

Now I have some code which is kind of working.

This code:

DELETE p, a, n
            FROM Posts p
            INNER JOIN Activity a ON a.uuidPost = p.uuid
            LEFT OUTER JOIN Notifications n ON n.uuidPost = p.uuid
            WHERE p.uuid = '$uuid'

Deletes the post, but only if there is some activity(for ex. likes or comments), but if there are no likes or comments It does not delete the post...

I could do with some help trying to understand why this is happening if possible?!

Many thanks in advance to anyone that can spare some time for me!

INNER JOIN only finds rows if they match in both tables. That's the difference between INNER JOIN and OUTER JOIN. — Barmar
– Barmar, Commented Jan 13, 2017 at 16:41
@Barmar Hello, so what would you recommend? I just change the 'INNER JOIN' to 'OUTER JOIN' and it worked... Would that be correct? — user5590595
– user5590595, Commented Jan 13, 2017 at 16:46

Barmar · Accepted Answer · 2017-01-13 16:46:49Z

1

When you use INNER JOIN, it only returns rows that have matches between the two tables, and DELETE only deletes rows that are returned by the JOIN. If you want to delete posts even if they have nothing matching in Activity, you should use LEFT OUTER JOIN -- this will return rows from the first table even if they have no matches.

DELETE p, a, n
FROM Posts p
LEFT OUTER JOIN Activity a ON a.uuidPost = p.uuid
LEFT OUTER JOIN Notifications n ON n.uuidPost = p.uuid
WHERE p.uuid = '$uuid'

BTW, if you declare uuidPost as foreign keys with ON DELETE CASCADE, you don't need to use the joins at all -- the CASCADE option automatically deletes the related rows in those tables when you delete the post.

answered Jan 13, 2017 at 16:46

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

1 Comment

user5590595 Over a year ago

Fantastic! Thanks a lot, your second option sounds interesting...Would you recommend changing my current query? or would they work exactly the same?

Collectives™ on Stack Overflow

MYSQL Delete multiple or single row from different columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related