Solved

Help with DELETE statement

Posted on 2008-10-29
2
221 Views
Last Modified: 2010-03-20
I have two tables joined by a composite key. (COMPANY_CD and RR_CD).
I want to delete all records from Table 1 where the record on Table 2 has a NULL value for e-mail address.

i.e.
DELETE [100_Outlook_Contacts].*, Missing_IA_Codes.[E-mail address]
FROM 100_Outlook_Contacts INNER JOIN Missing_IA_Codes ON ([100_Outlook_Contacts].RR_CD = Missing_IA_Codes.RR_CD) AND ([100_Outlook_Contacts].COMPANY_CD = Missing_IA_Codes.COMPANY_CD)
WHERE (((Missing_IA_Codes.[E-mail address]) Is Null));

0
Comment
Question by:NO_CARRIER
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 9

Accepted Solution

by:
jamesgu earned 75 total points
ID: 22832196
delete from 100_Outlook_Contacts
where exists (
             select 1
             from Missing_IA_Codes
             where [100_Outlook_Contacts].RR_CD = Missing_IA_Codes.RR_CD
              AND [100_Outlook_Contacts].COMPANY_CD = Missing_IA_Codes.COMPANY_CD
                  and Missing_IA_Codes.[E-mail address] Is Null
0
 
LVL 1

Author Comment

by:NO_CARRIER
ID: 22832437
works awesome... that was way over my head.
Thanks!
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question