Sql delete from A if exist in B

Question

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.

possible duplicate of [SQL DELETE with JOIN another table for WHERE condition](http://stackoverflow.com/questions/1980738/sql-delete-with-join-another-table-for-where-condition) — Thomas Ruiz, Oct 17 '13 at 13:00
I want to delete it. Mailing table should not have emails exist users — Wordica, Oct 17 '13 at 13:02

score 2 · Accepted Answer · answered Oct 17 '13 at 13:01

2

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

answered Oct 17 '13 at 13:01

Hamlet Hakobyan

score 0 · Answer 2 · answered Oct 17 '13 at 13:01

0

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

answered Oct 17 '13 at 13:01

Sashi Kant

score 0 · Answer 3 · answered Oct 17 '13 at 13:02

0

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);

answered Oct 17 '13 at 13:02

Vishwanath Dalvi

score 0 · Answer 4 · answered Oct 17 '13 at 13:47

0

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

answered Oct 17 '13 at 13:47

Rajesh Paul