Delete from 2 tables one-to-many relation

Question

I have 3 tables: articles:

id title content date

tags:

id  name

tags_in_news:

id  news_id  tag_id

news_id is foreign key for articles table and tag_id is foreign key for tags tables...How to delete an article?? I tried but did not receive,my code:

$aid=(int)$this->uri->segment(3,0);
 $this->db->query("DELETE * FROM articles, tags_in_news WHERE articles.id = $aid AND tags_in_news.news_id = $aid ");

Help me please ;)

score 2 · Answer 1 · edited May 23 '17 at 12:05

2

Try this:

DELETE a, tn 
FROM articles a INNER JOIN 
     tags_in_news  tn
WHERE a.id=tn.news_id 
    AND a.id = $aid 
    AND tn.news_id = $aid

Another option:

You can define foreign key constraints on the tables using ON DELETE CASCADE.

Then deleting the record from parent table removes the records from child tables. Read more here.

Credit: SO answer.

edited May 23 '17 at 12:05

Community

1
1

answered May 07 '14 at 13:30

Raging Bull

18,593
13
50
55

I define ON DELETE CASCADE – user3611170 May 07 '14 at 13:37

score 1 · Accepted Answer · edited May 23 '17 at 12:20

1

You can define foreign key constraints on the tables with ON DELETE CASCADE option.

Then deleting the record from parent table removes the records from child tables.

DELETE articles a ,tags_in_news t FROM articles INNER JOIN tags_in_news  WHERE a.id = t.id AND a.id = $aid

ref : Mysql - delete from multiple tables with one query

edited May 23 '17 at 12:20

Community

1
1

answered May 07 '14 at 13:29

StateLess

5,344
3
20
29

Delete from 2 tables one-to-many relation

2 Answers2