CODEWITHSUNDEEP
mysql
Wordica
Wordica

Reputation: 2597

Sql delete from A if exist in B

I have two tables:

MAILING table has one column email.

USER table has many users with email.

Now I want to delete something like this:

DELETE email FROM mailing WHERE users.email = mailings.email

So i need to delete email from table mailing if this email exist in user table.

Upvotes: 0

Views: 711

Answers (4)

Rajesh Paul
Rajesh Paul

Reputation: 7009

You can use EXISTS clause of MySQL as follows-

delete from mailing where exists(select * from user where user.email=mailing.email);

No Need of using JOIN

Upvotes: 0

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36611

Considering you want to delete all rows from mailing table, and you've only one column in mailing table.

Delete
from mailing where mailing.email 
exists (select email from users where users.email = mailing.email);

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

You can use this

DELETE FROM mailing WHERE email IN (SELECT email FROM users)

or this using JOIN

DELETE M
FROM mailing M
  JOIN users U
    ON M.email = U.email

Upvotes: 2

Sashi Kant
Sashi Kant

Reputation: 13465

Try update not delete if you want to update a column

UPDATE mailing 
inner join users on users.email = mailings.email  
set email=null

or you want to delete the records TRy this:

DELETE m 
FROM mailing m 
inner join users u on u.email=m.email

Upvotes: 0

Related Questions