CODEWITHSUNDEEP
mysql
georgevich
georgevich

Reputation: 2495

Delete from two tables with join?

I have two tables as follows

tbl1              tbl2
id                article_id
title,            image
whole_news
tags,
author,
older (datetime)

where tbl1.id -> tbl2.article_id

How to delete records from both tables where older is < 2008-02-10 00:00:00 ?

Upvotes: 5

Views: 4422

Answers (3)

Brandon Horsley
Brandon Horsley

Reputation: 8114

See my answer to a similar question here.

To summarize, it would look like

 delete s, r from tbl1 s left join tbl2 r on s.id = r.article_id where s.older < str_to_date('2008-02-10 00:00:00', '%Y-%m-%d %H:%i:%S');

But the better solution would be a foreign key constraint with an on delete cascade, if that is an option, then just delete from tbl1 with the appropriate where clause.

Upvotes: 10

w.k
w.k

Reputation: 8386

Simplest way: You should use FOREIGN KEYS With ON DELETE CASCADE.

Upvotes: 0

Distdev
Distdev

Reputation: 2312

You can use triggers

Upvotes: 0

Related Questions