How to remove row that exists in another table?

Question

I have two tables. Main table is "CompleteEmailListJuly11" and the second table is "CurrentCustomersEmailJuly11". I want to delete rows in CompleteEmailListJuly11 table that CurrentCustomersEmailJuly11 has based off email.

I've tried this following Delete example, but it doesn't do anything close to what I'm trying to do. This only shows me the ones that EXIST in the database, it doesn't show me the the list of emails that AREN'T matching.

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

Help is greatly appreciated.

Croton

What do you mean it shows you ones that exist in the database... it's a DELETE. DELETE commands aren't supposed to show you anything... they are supposed to delete things. – Photomultiplier

You made "i" an alias for CompleteEmailListJuly11. You may not be able to refer to that table as "i" in the nested SELECT statement. – Tlaxcala

@Smandoli: why not? It's a correlated subquery. Whether it's the right thing for his needs is another question, but this sort of thing should be strictly legal. – Subject

I must be missing something then. Yea its a delete statement, but in Access 2007 it doesn't delete anything it just shows me the matching items. Then when i try to do an Export based off of the query i have listed above, it still only shows the items that SHOULD be deleted. – Croton

Nevermind apparently the macro's was set to be disabled sigh. <- newb – Croton

Thanks for the correction. I said "You may not be able to refer to that table as 'i' ..." -- that's not true. – Tlaxcala

Answer 1

This is the query I think you need:

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

Ps: The DELETE query does not delete individual fields, only entire rows, so the * is not necessary, you will also need to "Execute" this query rather than "Previewing" or "Exporting"

Cathartic

Answer 2

If you're building your DELETE query in Access' query designer, notice there are two different modes of operation which seem similar to "go ahead and do this".

1. Datasheet View (represented by the grid icon labeled "View" on the "Design" section of the ribbon). That view enables you to preview the affected records, but does not actually delete them.
2. The "Run" icon (represented by a red exclamation point). "Run" will actually execute the query and delete the affected records.

If you already know this, my description may seem insulting. Sorry. However, it seems that folks new to Access can easily overlook the distinction between them.

Amblyoscope

+1 As you say, easy to overlook if you haven't spent much time with Access – Cathartic

Answer 3

You can use something like this adapted to delete

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

I am not sure exactly how it maps to delete but take a look at that.

I fond it in a similar question: How do I 'subtract' sql tables?

Punner

Answer 4

We can use Correlated Query to resolve the issue like

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

Sybilla