CODEWITHSUNDEEP
mysql
Ted
Ted

Reputation: 4166

MySQL: remove rows from table if exist in other table with condition

I need to remove rows from "t2", if "t2.t1_id" exists in "t1" and "t1.status = E"

tables structure,

t1 (structure)

id      status
1         E
2         A
3         A
4         E
5         C

t2 (structure)

id     t1_id
1           1
2           2
3           3
4           4
5           5

note: t1_id forign key refer to table_1.id

thanks,

Upvotes: 1

Views: 367

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13527

Try this:-

DELETE t2,t1
FROM t1
INNER JOIN t2 ON t1.id = t2.t1_id
WHERE t1.status = 'E';

Upvotes: 1

Paul Lo
Paul Lo

Reputation: 6148

Answer:

DELETE FROM t2 WHERE t1_id IN (
    SELECT id FROM t1 WHERE status='E'
)

The expected result after the query would be:

id     t1_id
2           2
3           3
5           5

Upvotes: 2

Related Questions