DELETE query with OUTER JOIN on sub-query (AC365) (1 Viewer)

[AOB]

Hi all,

Have a suspicion what I'm trying to do is simply not possible in Access (as opposed to SQL Server) but figured I'd ask before completely scrapping it.

I have a table - consisting of two fields of ID's (basically a junction table) - but no formal relationships, so no foreign keys etc. and I want to delete redundant pairs from this table using a sub-query.

In effect, I want to remove any records where the ID-pairs aren't present in this query (this query works fine / as expected) :

SELECT R.RID, O.OID
FROM ((tblR R
INNER JOIN tblS S ON R.RID = S.RID)
INNER JOIN tblA A ON S.SID = A.SID)
INNER JOIN tblO O ON A.AID = O.AID

So I can identify the records in the main table that I want to delete, using this query (this query also works fine / as expected) :

SELECT J.RID, J.OID
FROM tblJ J
LEFT JOIN
    (SELECT R.RID, O.OID
     FROM ((tblR R
     INNER JOIN tblS S ON R.RID = S.RID)
     INNER JOIN tblA A ON S.SID = A.SID)
     INNER JOIN tblO O ON A.AID = O.AID) T
ON J.RID = T.RID AND J.OID = T.OID
WHERE T.RID IS NULL AND T.OID IS NULL

However, if I attempt to delete those records from the main table, it fails :

DELETE DISTINCTROW tblJ.*
FROM tblJ
LEFT JOIN
    (SELECT R.RID, O.OID
     FROM ((tblR R
     INNER JOIN tblS S ON R.RID = S.RID)
     INNER JOIN tblA A ON S.SID = A.SID)
     INNER JOIN tblO O ON A.AID = O.AID) T
ON tblJ.RID = T.RID AND tblJ.OID = T.OID
WHERE T.RID IS NULL AND T.OID IS NULL

With an error of :

Could not delete from specified tables

So - I guess I'm just trying to determine if what I'm doing simply isn't possible with the Access DB engine, or if it's just something dumb I'm overlooking?

Obviously I have the option of just creating a separate table, dumping the valid/invalid records into that, then using that for a direct join in a delete operation on the main table (inner or outer depending on whether that table has valid or invalid records) and then dump that temp table. It's just a bit more clunky when I can very quickly and easily identify the records that need to be deleted with one simple sub-query. So would just prefer to do it in one SQL command if at all possible.

Any suggestions?

Thanks!

AOB

 

[Uncle Gizmo]

I notice you have not yet received a reply, hence I am bumping your question up the list