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?
Just open the table in Datasheet view, select the fields (columns) or records (rows) that you want to delete, and then press DELETE.
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.
This should work:
DELETE TableB.Text, TableA.*
FROM TableA
INNER JOIN TableB ON TableA.BID = TableB.BID
WHERE TableB.Text="foo";
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With