CODEWITHSUNDEEP
sqldelete-row
A. Nelson
A. Nelson

Reputation: 9

SQL statement, delete records where 2 fields do not match 2 fields from different table

SQL newbie here: I have a temp table called work1 with the following fields:

company3, dept. (and other fields that are n/a to the question)

I have a manager association table 'deptmgrassoc' that has the following fields that identify which combination of department & company a manager is over:

company3, dept, mgrid.

Let's say work1 has the following records:

company3, dept
1, a
1, b
1, c
2, a
2, b
2, c

How do I delete records from work1 where the company3 & dept combination are not in the list of company & dept combinations in the association table for Tom where his records are:

company3, dept, mgrid
1, a, tom
1, b, tom
2, a, tom
2, b, tom

The sql should delete records with 1, c & 2, c since those combinations are not in the association table.

Upvotes: 1

Views: 32

Answers (1)

Mureinik
Mureinik

Reputation: 311228

The not exists operator should do the trick:

DELETE FROM work1 w
WHERE  NOT EXISTS (SELECT *
                   FROM   deptmgrassoc d
                   WHERE  w.company3 = d.company3 AND
                          w.dept = d.dept)

Upvotes: 1

Related Questions