Delete Query - Use Another Table As Criteria

[Madmartigan]

I have two tables:

• Temp Table
  
  • Contains the data (rows) that I want to delete using a delete query.
  
  
• Excluded Customers
  
  • Contains a list of customers whose records I want to remove from the temp table.

Here is the SQL (although I'm doing this in design view):

DELETE [tSAP Temp Table].[Branch account]
FROM [tExcluded Customers] INNER JOIN [tSAP Temp Table] ON [tExcluded Customers].[SOLD TO#] = [tSAP Temp Table].[Branch account];

When I go to datasheet view, the query correctly selects the records that I want deleted from the tempt table. But when I run the delete query, I get the error: Specify the table containing the records you want to delete

I want to remove all rows from the temp table where there is a join on branch account (temp table) and Sold To# (excluded customers) table.

I'm not the best with SQL but my stab at it would be something like this:

DELETE [tSAP Temp Table].[Branch account]
FROM [tSAP Temp Table] 
Where [tExcluded Customers].[SOLD TO#] = [tSAP Temp Table].[Branch account];

[Dal Jeanis]

Okay, you're trying to delete records, not fields, right?

You're probably looking for one of these two, depending on which table you're trying to delete records from.

DELETE * 
FROM [tExcluded Customers] 
WHERE [tExcluded Customers].[SOLD TO#] IN 
  (SELECT [Branch account] 
   FROM [tSAP Temp Table]);

DELETE * 
FROM [tSAP Temp Table]
WHERE [tSAP Temp Table].[Branch account] IN
  (SELECT [SOLD TO#] 
   FROM [tExcluded Customers]);

Warning: As always, back up your real database and try this in a test version before you do it in the real database.

[ssanfu]

Edit: Use what Dal provided
--------


You need both tables in the FROM clause.

I'm not the best at this. It usually takes me a couple of tries to get the query to do what I want, not what I tell it to do.

Try this:

Create a new query in design view.
Add both tables. The ahould be linked by the fields
[tExcluded Customers].[SOLD TO#] and [tSAP Temp Table].[Branch account]

In the first column of the grid, add the "*" from table [tSAP Temp Table]
In the 2nd column of the grid, add the field [Branch account].

In SQL view, your query should look like:

SELECT [tSAP Temp Table].*, [tSAP Temp Table].[Branch account]
FROM [tExcluded Customers] INNER JOIN [tSAP Temp Table] ON [tExcluded Customers].[SOLD TO#] = [tSAP Temp Table].[Branch account];

Change back to Design view.
Now add a criteria to the 2nd column:[tExcluded Customers].[SOLD TO#]

In SQL view, you should have:

SELECT [tSAP Temp Table].*, [tSAP Temp Table].[Branch account]
FROM [tExcluded Customers] INNER JOIN [tSAP Temp Table] ON [tExcluded Customers].[SOLD TO#] = [tSAP Temp Table].[Branch account]
Where [tExcluded Customers].[SOLD TO#] = [tSAP Temp Table].[Branch account];

Change back to Design view. Change the query to a DELETE query.
In the 3rd row (Delete) of the first column, you should see "FROM"
In the 3rd row (Delete) of the second column, you should see "WHERE"

In SQL view, you should have:

DELETE *
FROM [tSAP Temp Table] INNER JOIN [tSAP Temp Table] ON [tExcluded Customers].[SOLD TO#] = [tSAP Temp Table].[Branch account]
Where [tExcluded Customers].[SOLD TO#] = [tSAP Temp Table].[Branch account];

Again the warning..... Try this on a COPY of the dB. I still struggle with creating delete queries using joined tables. I don't create them very often.

This query will (should) delete the entire row of data, not just the data from one field.

[Dal Jeanis]

This thread http://stackoverflow.com/questions/5...en-using-joins suggests

Delete Table1.*
From Table1
Where Exists( Select 1 From Table2 Where Table2.MatchName = Table1.MatchName ) = True

which seems roughly like an IN test. So, I'd expect my code to work.

It also suggests that when using the JOIN format, using DISTINCTROW helps Access from getting confused about whether the join might return duplicate copies of the item to be deleted.

[Madmartigan]

Okay, you're trying to delete records, not fields, right?

Correct.

 DELETE * 
 FROM [tSAP Temp Table]
 WHERE [tSAP Temp Table].[Branch account] IN
   (SELECT [SOLD TO#] 
    FROM [tExcluded Customers]);

This code looks like it did the trick!! Thank you for the replies!!