How to delete messages from database where their thread doesn't exist anymore

I've got a problem with something I written to keep track of stuff, a couple months ago. You can add messages to tasks, and the tasks get completed, the messages all remain, that's fine, so tasks that are incomplete, or complete have their messages associated by the task_id in the messages table. The problem is, if someone deletes a task, it doesn't delete the messages that were attached to it by it's ID.

So I've now got lots of messages that don't have an "owner", are just lying redundant in the database so I need to somehow delete all from messages where messages.task_id doesn't exist in job.id开发者_开发问答. Hope my pseudo code explains what I'm in need of doing.

Thanks.

---

First, back up the Messages table.

Then try this to see if the messages selected make sense to delete

Select * from Messages where Task_Id NOT IN (Select Task_Id From Tasks)

If they do, then Delete

Delete from Messages where Task_Id NOT IN (Select Task_Id From Tasks)

If you can enforce Refential Integrity between Tasks and Messages using a Foreign Key, you will avoid the "Orphaned Records" issue in the future.

---

Raj is on-spot here. You might also want to learn about FOREIGN KEYS and ON DELETE CASCADE, which are designed to prevent exactly this kind of problem. See the handbook at http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

---

delete from messages m left join job j on (j.id = m.task_id) where j.id is null

Also, consider adding a foreign key to enforce referential integrity, so you'll not end up with orphan records again.

alter table messages add constraint foreign key task_id references job.id on delete cascade