Solved

deleting records  from a table based on the fields of another table ms sql

Posted on 2011-09-27
1
211 Views
Last Modified: 2012-05-12
I have a table1 with field1 and a table2 with field2

I want to delete all records in table1  that do not have a match in table 2 (if john is in table1 and not in table2 I want it deleted) I have done it in very complicated ways using left joins, temprary tables.. but there must be an easier way
0
Comment
Question by:robrodp
1 Comment
 
LVL 9

Accepted Solution

by:
edlunad earned 500 total points
ID: 36713906
Try this...

DELETE FROM Table1
WHERE Field1 NOT IN 
    (SELECT Field2 
     FROM Table2)

Open in new window

0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.​
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now