-4

I have 2 tables, one with customers, containing their name, email, company, etc. I have another table that just contains the emails of the customers that need to be removed. I need to develop a query to delete all rows from the first table if they contain an email in the second. These tables are in a Microsoft SQL Server database.

As I am in the process of learning SQL I have no idea how to do this, I've searched through questions and can't find a similar enough one.

H M
  • 13
  • 1
  • Sounds like the common factor for the two is the email address, you would look at joining on the email address, to find only those where the email address is present in your table that holds the addresses to be deleted. – Keith Aug 29 '17 at 15:52
  • We don't offer free code writing service. At least try to attempt something and post it here. If it doesn't work, we can help you fix it. – Eric Aug 29 '17 at 15:53
  • 1
    Hint: use `EXISTS`. – Eric Aug 29 '17 at 15:53
  • 1
    You didn't search very hard. I found a duplicate in 5 seconds. – Tab Alleman Aug 29 '17 at 15:53
  • 2
    I googled the title of your question and the first hit was the answer: – Dan Guzman Aug 29 '17 at 15:54

2 Answers2

1

You can use exists() like so:

delete c
from customers as c
where exists (
  select 1
  from emails as e
  where e.email = c.email
  )

or in():

delete c
from customers as c
where c.email in (select email from emails)
SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • I'm not sure why this was DV. Just because there may be a duplicate elsewhere doesn't make this incorrect and warrant a DV IMHO. – S3S Aug 29 '17 at 15:58
  • @scsimon Thanks for the equalizing upvote. It seems some people downvote answers to bad/duplicate questions because of the question, not the answer.[stackoverflow meta - Is it okay to downvote answers to bad questions?](https://meta.stackoverflow.com/q/255459/2333499) -- Happened to me earlier today on this one too: [Get Room Price of Overlapping dates](https://stackoverflow.com/q/45938635/2333499) – SqlZim Aug 29 '17 at 16:02
  • 1
    @scsimon Agreed. Don't punish the answerer for a duplicate or poorly written question. This also goes against "When should I downvote". SMH. – Simon Aug 29 '17 at 16:02
  • https://meta.stackoverflow.com/questions/252009/should-there-be-a-deterrent-for-answering-obvious-duplicate-questions – Tab Alleman Aug 29 '17 at 18:16
  • @TabAlleman I'm not sure what you are trying to convey by posting the link without any other comment. – SqlZim Aug 29 '17 at 18:27
  • Just pointing out a meta discussion where the majority opinion (as evidenced by upvotes anyway) runs counter to the majority opinion in the meta discussion you cited. I'm only trying to convey that there are two camps of opinion about downvoting answers to bad questions, and neither camp is expressly wrong or right in rules of SO. – Tab Alleman Aug 29 '17 at 18:29
  • @TabAlleman I don't see any answers posted on that meta question where downvoting the answers is the suggested action. The top answer suggests rewarding those who find duplicates. The second highest answer's suggestion is to simply not upvote the answer. -- Regardless, my point in citing the meta quest I did was to point out that some people do choose to downvote answers to bad questions, and while ...1/2 – SqlZim Aug 29 '17 at 18:38
  • 2/2... the top answer on the question I cited does not think it is appropriate, it does note that "everyone is free to vote as they please", which I accept and respect. If you are downvoting my answers because you think the question is a poor/bad/duplicate question, I accept that. I would appreciate a comment to that effect, just so I know why it was being downvoted, but I also understand there are reasons why one would choose not to do that as well. – SqlZim Aug 29 '17 at 18:38
1

Use an IN clause:

Delete from [customers] where [email] in (Select [email] from [toberemoved])
cloudsafe
  • 2,444
  • 1
  • 8
  • 24