Delete records whose couple of values not present in another table 1

[polocar]

Hi,
I have two Access tables (we can call them Table1 and Table2) with the same structure, that is 4 fields: IDRecord, num1, num2, num3.

I would like to delete from Table2 the records whose couples of values num1, num2 are not present in any record
of Table1.
For example, suppose that Table1 has

IDRecord num1 num2 num3
1 3 6 5
4 5 8 4
6 2 3 3

and Table2 has

IDRecord num1 num2 num3
1 5 8 6
3 3 6 4
5 2 4 3

Table 2 first record (IDRecord = 1) has (5, 8) as couple of values for num1 and num2 fields (this couple is present
in Table1 second record (IDRecord = 4), so I don't want to delete it.
Similarly, Table 2 second record (IDRecord = 3) has (3, 6) as couple of (num1, num2) values (this couple is present
in Table1 first record (IDRecord = 1), so the record musn't be deleted.
Table2 third record (IDRecord = 5) has (2, 4) as couple of values, and it's not present in anyone of the records of
Table1, so I want to delete the third record of Table2.

What is the SQL statement that allows to do that?

Thank you very much

 

[r937]

I would like to delete from Table2 the records whose couples of values num1, num2 are not present in any record of Table1.

delete
  from Table2
 where not exists
       ( select *
           from Table1
          where num1 = Table2.num1
            and num2 = Table2.num2 )

neat how the sql looks so very close to the statement of requirements, eh?



r937.com | rudy.ca

 

[polocar]

Thank you r937, your code functions correctly.
Yesterday I casually have discovered another way to do that (perhaps more intuitive):

delete from Table2
where (num1, num2) not in
(select num1, num2 from Table1)


Thank you

 

[r937]

yes, very intuitive, but sadly not supported in very many database systems

r937.com | rudy.ca