Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MS-Access: Selecting rows to delete via joins

Tags:

ms-access

I found this question that is discussing what I would like to do, but it's for T-SQL. Is there a way to do something similar in Microsoft Access? The examples below are based off what I found in that question.

I need to delete all the records in TableA, which is linked to TableB via field Bid based on another field in TableB.

Here is the query that selects the items to be deleted:

SELECT * 
FROM TableA a
INNER JOIN TableB b on b.Bid = a.Bid
WHERE [my filter condition]

The following query results in an error "Specify the table containing the records you want to delete."

DELETE TableA 
FROM TableA a
INNER JOIN TableB b on b.Bid = a.Bid
WHERE [my filter condition]

Is this possible with an Access query?

like image 765
CoderDennis Avatar asked May 20 '09 18:05

CoderDennis


People also ask

How do you select and DELETE multiple rows in Access?

Just open the table in Datasheet view, select the fields (columns) or records (rows) that you want to delete, and then press DELETE.

Does inner join remove rows?

MySQL also allows you to use the INNER JOIN clause in the DELETE statement to delete rows from a table and the matching rows in another table. Notice that you put table names T1 and T2 between the DELETE and FROM keywords. If you omit T1 table, the DELETE statement only deletes rows in T2 table.


1 Answers

This should work:

DELETE TableB.Text, TableA.*
FROM TableA 
INNER JOIN TableB ON TableA.BID = TableB.BID
WHERE TableB.Text="foo";
like image 182
Oorang Avatar answered Oct 12 '22 12:10

Oorang