CODEWITHSUNDEEP
mysql
kojow7
kojow7

Reputation: 11384

Delete record based on condition in parent table

I have the following tables defined in my database:

moderator (id, name)

parent_object (id, moderator_id, parent_name)

child_object (id, parent_id, child_name, quantity)

I want to delete a child object based on the ID. I currently have something like this which works: 

delete from child_object where id = 3;

However, there is a security hole because this allows any moderator to update any child object that they do not own. If the moderator_id is 2, how can I delete this record so that it will only delete it if the moderator_id of the parent_object is 2?

Upvotes: 0

Views: 109

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use the following solution using INNER JOIN:

DELETE c.* FROM child_object c 
    INNER JOIN parent_object p ON c.parent_id = p.id  
WHERE c.id = 3 AND p.moderator_id = 2

demo: http://sqlfiddle.com/#!9/7290b5/1/0

Upvotes: 2

Related Questions