How to remove row that exists in another table?

sql ms-access ms-access-2007
eqiz picture eqiz · Jul 11, 2012 · Viewed 16.5k times · Source

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.

Answer

Matt Donnan picture Matt Donnan · Jul 12, 2012

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"

Related questions

A select query selecting a select statement

I don't even know if I am doing this query the right way. There is a Sandwiches table that has some 7 fields and 2 of them are comboboxes (Type and Bread). So I made a query that combines all of the …

Read More
How do I correctly use "Not Equal" in MS Access?

Objective: The intent of this query is to select all of the distinct values in one column that don't exist in a similar column in a different table. Current Query: SELECT DISTINCT Table1.Column1 FROM Table2, Table1 WHERE Table1.Column1 &…

Read More
Check if access table exists

I want to log web site visits' IP, datetime, client and refferer data to access database but I'm planning to log every days log data in separate tables in example logs for 06.06.2010 will be logged in 2010_06_06 named table. When date …

Read More