How to Delete in Ms Access When Using Join'S

How to delete in MS Access when using JOIN's?

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

To expand on my answer, the official SQL specification does not provide for using Joins in action queries specifically because it can create ambiguous results. Thus, it is better (and Access is much happier) if you can avoid using Joins in action queries like I have here. The reason that Access wants DISTINCTROW is that it is likely that the Join between the two tables would create duplicates of Table1 rows (i.e., there are multiple related rows in Table2) and thus Access gets confused. I've also found that if you try to use a Join and a primary key does not exist Access will balk. In general, it is better to avoid a join in an action query if you can.

Ms Access Delete Query with Join and Where Clause

Delete records only in tblTrainingElements table:

DELETE *
FROM tblTrainingElements
WHERE tblTrainingElements.[Course ID] IN (SELECT tblCourses.[Course ID] FROM tblCourses WHERE tblCourses.[App ID]="CAD" );

MS ACCESS delete query syntax combined with inner join problems

When you join the two tables you can create some ambiguity as to what you want to delete, for example you may have rows in the first table that have many matches in the second table. Once joined there will be many copies of the row from the first table in the joined table and when you try and delete the entries from the first table using the joined one this confuses Access.

There are a couple of solutions:

1) Use DistinctRow: This will stop the previous problem, as it will mean each row in the joined table is unique and prevent the previous problem:

DELETE DISTINCTROW b.* FROM T_Value b  
INNER JOIN T_Value AS a ON b.MeasTime = a.MeasTime 
AND b.JobId = a.JobId 
WHERE b.DataId > a.DataId

2) Use a subquery: Although subqueries can be slow, this prevents any ambiguity in results, and stops you having to delete duplicate rows:

DELETE b.* FROM T_Value b   
WHERE b.DataId > a.DataId
AND EXISTS ( SELECT 1 FROM T_Value WHERE MeasTime = b.MeasTime ) 
AND EXISTS ( SELECT 1 FROM T_Value WHERE JobId = b.JobId ) 

MS-Access: Selecting rows to delete via joins

This should work:

DELETE TableB.Text, TableA.*
FROM TableA 
INNER JOIN TableB ON TableA.BID = TableB.BID
WHERE TableB.Text="foo";

Error running MS Access delete query with left join

You can do what you want with NOT EXISTS:

DELETE Contacts.* 
FROM Contacts 
WHERE Contacts.ProcurePreference = 'Out of Business' 
  AND NOT EXISTS (SELECT 1 FROM Donations WHERE Contacts.[ContactID] = Donations.[DonorID])

Error executing MS Access Delete Query with Left Join

DELETE
FROM tblParts 
WHERE NOT EXISTS
(
SELECT '1'
FROM tblOrders 
WHERE tblOrders.PartID = tblParts.ID
);

---

Related Topics