CODEWITHSUNDEEP
mysql
user5590595
user5590595

Reputation:

MYSQL Delete multiple or single row from different columns

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!

Upvotes: 0

Views: 86

Answers (1)

Barmar
Barmar

Reputation: 780889

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.

Upvotes: 1

Related Questions