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

Question

I am attempting to use the DELETE clause in MS Access and have an issue when also using the JOIN clause. I have notice this can be accomplished by using the DISTINCTROW key word.

For example, the following SQL statement does not allow for deletion:

DELETE Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.Name=Table2.Name;

However, this statement does:

DELETE DISTINCTROW Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.Name=Table2.Name;

• Why does the DELETE work when using the DISTINCTROW key word?
• More specifically, what is happening in the JET engine to require this?

Kapp

Considering that DELETE operates on whole rows rather than columns, specifying anything between the DELETE and FROM keywords makes no sense. – Phonetics

Answer 1

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.

Grof

Your answer is precisely correct -- the whole purpose of DISTINCTROW is to resolve a multitable into unique rows in a way that makes them editable. – Chancellorsville

I would like to add that the query provided above may have extremely poor performance when using cross database (or Access to other database) joins which does not occur with DISTINCTROW. (in one case it was 20sec vs not finished after 1h) – Murtagh

@Murtagh - An alternate version of the same query would be Where PKCol In(...) = True which eliminates the correlation and even in cross-database scenarios should perform well. Because there is no ability to analyze the execution plan in Access, there is no means to easily determine what DISTINCTROW is really doing and what it is doing poorly when using the Exists function. We are left to use trial and error. – Grof

This query can be simplified by removing '= True' redundant comparison. – Hyams

In theory...yes. However, I seem to remember old versions of Access effectively requiring the explicit comparison to a boolean constant. – Grof

Worked like a charm. (It's always the seemingly-simple tasks I get hung up on.) I often forget Access allowed sub-query usage in the Query Design window. – Hammerskjold

Answer 2

One problem to be aware of: This does NOT work with table/query aliases!

DELETE a.*
from tblA as A
where exists (select 1 from tblB as B where a.id=b.id)

Deletes ALL records in tblA! I tried it using alias for tblA and tblB seperately - same result (Access 2010).

Happens with SELECT as well (which I often use before deleting)...

Waly

Answer 3

DELETE a.*
FROM tblA AS A
WHERE EXISTS (SELECT 1 FROM tblB AS B WHERE a.id=b.id)

try this

DELETE tblA 
FROM tblB  
WHERE EXISTS (SELECT * FROM tblA AS A,tblB AS B WHERE A.id=B.id)

Zenda

Delete from tblA where id in (Select id from tblB) – Zenda

for join table: – Zenda

DELETE * FROM ttrans WHERE exists (select a.* from ttrans a,temp_tmbtrans b where ttrans.ref_code = b.ref_code and ttrans.fund_account = b.fund_account and ttrans.tr_date = b.tr_date and ttrans.tr_code = b.tr_code and ttrans.sharecode = b.sharecode and ttrans.unit = b.unit and ttrans.amt = b.amt and ttrans.price = b.price and ttrans.account = b.account); – Zenda

In response to the comment above, be aware that MS Access does not support Temporary tables. – Kight

Answer 4

Just set the property of query to Unique Records YES and it will work.

Barajas

Answer 5

Try using this:

DELETE Table.* FROM Table
WHERE ID IN (SELECT ID FROM TableOrQuery2)

Bcd

Answers that contain only code are generally considered to be poor quality. Take the tour and visit the help center and learn how to use markdown. – Entryway