By Curtis Inderwiesche


2011-04-07 18:23:03 8 Comments

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?

3 comments

@user7047561 2016-10-20 11:09:53

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)

@user7047561 2016-10-20 11:49:16

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

@user7047561 2016-10-21 03:05:57

for join table:

@user7047561 2016-10-21 05:09:18

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);

@MarcusFey 2014-06-03 12:14:10

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)...

@Thomas 2011-04-07 18:27:28

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.

@David-W-Fenton 2011-04-09 22:44:41

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.

@Stefan 2012-09-04 19:42:54

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)

@Thomas 2012-09-04 20:12:20

@Stefan - 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.

@Stefan 2012-09-05 12:18:34

@Thomas Going to try that :D

@Mariusz Bialobrzeski 2018-01-23 17:09:59

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

@Thomas 2018-01-24 19:48:38

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

@ashleedawg 2018-03-27 15:55:45

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.

Related Questions

Sponsored Content

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

8 Answered Questions

[SOLVED] How does database indexing work?

17 Answered Questions

[SOLVED] SQLite - UPSERT *not* INSERT or REPLACE

  • 2009-01-07 01:47:23
  • Mike Trader
  • 273144 View
  • 502 Score
  • 17 Answer
  • Tags:   sql sqlite upsert

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

1 Answered Questions

Microsoft Access: Related Table and Self/Outer Join

  • 2018-05-05 14:48:54
  • Leo Evans
  • 43 View
  • 0 Score
  • 1 Answer
  • Tags:   ms-access

4 Answered Questions

2 Answered Questions

1 Answered Questions

[SOLVED] Convert SQL query to access query

  • 2014-06-12 14:53:44
  • user3490323
  • 78 View
  • -1 Score
  • 1 Answer
  • Tags:   mysql sql ms-access

Sponsored Content