Deleting from an Unmatched Query

[Peps]

I've created an Unmatched Query that works perfect, since the results from this query have to be deleted, I turned the query in question into a Delete Query. Once opened I get the following msg: Specify the table containing the records you want to delete. I'm a little stuck on so any help would be appreciated.

Peps

 

[lespaul]

standard DELETE syntax:

DELETE FROM TABLENAME WHERE WHATEVER = 'SOMETHING'

will delete every record in the table where field WHATEVER has the value of 'SOMETHING'

What is the SQL in your find unmatched query?

Leslie

 

[scriverb]

Give this SQL a try:

DELETE A.* FROM Table1 as A 
WHERE A.[ID] NOT IN (Select B.[ID] from Table2 as B);

This deletes all records from Table1 where there is no match in Table2 on matching field ID.

Post back if you have any questions.



Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Peps]

Hi Guys, Here's the original SQL code I was trying to use:

DELETE DISTINCTROW Req_Mail.Id, Vsl_Calls.Id
FROM Req_Mail LEFT JOIN Vsl_Calls ON Req_Mail.Id = Vsl_Calls.Id
WHERE (((Vsl_Calls.Id) Is Null));

I've tried to get Bob's code to fit in but to be honest I not sure how it's supposed to fit in. Here's the code after trying to inset bobs code:

DELETE Req_Mail.Id, Vsl_Calls.Id
FROM Req_Mail LEFT JOIN Vsl_Calls ON Req_Mail.Id = Vsl_Calls.Id
WHERE (((Req_Mail.Id) Not In ([Vsl_Calls].[Id])));

Peps

 

[scriverb]

Here is the updated code using your table and field names:

DELETE A.* FROM Req_Mail as A
WHERE A.[Id] NOT IN (Select B.[Id] from Vsl_Calls as B);

Post back with questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Peps]

Thanks Bob,

In two words: Fan Tastic.

Peps