How to delete a row existing in another table?

Question

I have two tables. The main table is "CompleteEmailListJuly11" and the second table is "CurrentCustomersEmailJuly11". I want to delete the rows in the CompleteEmailListJuly11 table that CurrentCustomersEmailJuly11 is based on email.

I tried this next Delete example, but it does not do anything close to what I am trying to do. It only shows me those that EXIST is in the database, it does not show me a list of letters that do NOT match.

DELETE * FROM CompleteEmailListJuly11 AS i WHERE EXISTS ( SELECT 1 FROM CurrentCustomersEmailJuly11 WHERE CurrentCustomersEmailJuly11.email = i.EmailAddress ) 

Help is appreciated.

Answer 1

This is a query that I think you need:

 DELETE FROM CompleteEmailListJuly11 WHERE EmailAddress IN (SELECT email FROM CurrentCustomersEmailJuly11) 

Ps: the DELETE query does not delete individual fields, but only whole lines, therefore * optional, you also need to execute the "Execution" of this query, and not "Preview" or "Export"

Answer 2

If you create your DELETE query in the Access query constructor, notice two different modes of operation that look like going ahead and doing this.

• Datasheet View (represented by a grid icon that says “View” in the “Design” section of the ribbon). This view allows you to view affected records, but does not actually delete them.
• Launch icon (represented by a red exclamation mark). Run will actually execute the request and delete the affected entries.

If you already know this, my description may seem offensive. I'm sorry. However, it seems that people new to Access can easily ignore the differences between them.

Answer 3

You can use something like this to remove

  SELECT ... // complete EXCEPT SELECT ... // current 

I don’t know exactly how to display it in order to delete, but take a look at it.

I like this question: How to "subtract" sql tables?

Answer 4

We can use a correlated query to solve a problem like

 DELETE FROM COMPLETE C WHERE EMAIL = (SELECT EMAIL FROM CURR CU WHERE CU.EMAIL=C.EMAIL);