CODEWITHSUNDEEP
sqlms-accessms-access-2007
eqiz
eqiz

Reputation: 1591

How to remove row that exists in another table?

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.

Upvotes: 7

Views: 18260

Answers (4)

Matt Donnan
Matt Donnan

Reputation: 5003

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"

Upvotes: 12

HansUp
HansUp

Reputation: 97111

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.

Upvotes: 1

Aspirant
Aspirant

Reputation: 2278

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

Upvotes: 0

hbhakhra
hbhakhra

Reputation: 4236

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?

Upvotes: 0

Related Questions