Delete with 2 keys

[shumaker]

I am using Access as a front end to SQL and have a combination of two
fields set as primary keys.

I have two tables, and am trying to delete all occurences of the
compound primary keys in one table, that do not occur in the second
table.

We can call the columns for the compound primary key Field3 and Field10
and the tables TableDel and TableRef. TableDel is the one to delete
items from, and TableRef is the table we reference for identifying
which items should be deleted.

This query produces the rows that I want to delete, but I can't figure
out how to make this a delete query.

SELECT TableDel.*
FROM TableDel LEFT OUTER JOIN
TableRef ON TableDel.Field3 = TableRef.Field3 AND
TableDel.Field10 = TableRef.Field10
WHERE (TableRef.Field10 IS NULL)
Any help would be greatly appreciated.

[Thomas R. Hummel]

The following should do the trick:

DELETE TD
FROM TableDel TD
LEFT OUTER JOIN TableRef TR ON TR.field3 = TD.field3
AND TR.field10 = TD.field10
WHERE TR.field10 IS NULL

If you can identify the rows with a select, then you just need to use
the alias in your delete statement like above and you should be good to
go.

-Tom.

[shumaker]

Awsome, I'll try that.
Thanks.