delete from 3 tables with one query

Question

i have 3 tables and i dont want define any foreign key in my tables. my tables structure are like below: tables diagram

i have written this query :

delete relativedata, crawls, stored
    from relativedata inner join
         crawls
         on relativedata.crawl_id = crawls.id and
            relativedata.id = ? inner join
         stored
         on stored.crawl_id = crawls.id

this query works for me unless one of tables has no records. now how can i do this delete in 3 tables in 1 query?

this query works when all tables have some thing to be joined. whats your mean? — Mahdi, Jan 03 '16 at 14:34
I mean, you can't delete selected columns from tables like the way you are trying. Not sure what exactly you are up to. Would suggest going through the documentation once. — Rahul, Jan 03 '16 at 14:35
according to this [link](http://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query) i can do such things — Mahdi, Jan 03 '16 at 14:38
The correct answer to the question in that link had two queries, not one. — Dan Bracuk, Jan 03 '16 at 14:53
please see this [link](http://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query/1233479#1233479) — Mahdi, Jan 03 '16 at 14:56
in my tables structure i want to remove records that has relative data. — Mahdi, Jan 03 '16 at 14:59
What database are you using? Please tag your question appropriately. — Gordon Linoff, Jan 03 '16 at 15:02

T.Z. · Accepted Answer · 2016-01-03T20:16:37.450

If it works if all tables have records, try using LEFT JOIN instread of INNER JOIN. Also, You had some mess with Your joins ON conditions. Try it like this:

delete 
    relativedata, crawls, stored
from
    relativedata 
LEFT join crawls on relativedata.crawl_id = crawls.id
LEFT join stored on relativedata.crawl_id = stored.crawl_id
WHERE
    relativedata.id = ?

Also, foregin keys are good thing, and not using them is generally bad idea. Yes, they seems to be annoying at first, but try to focus on WHEN they annoy You. Most of the times they do it when You are meddling with data in a way You should not, and without them You wloud cause data incostincency in Your DB.

But, it is just my opinion.

delete from 3 tables with one query

1 Answers1

Linked