30

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?
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Curtis Inderwiesche
  • 4,940
  • 19
  • 60
  • 82
  • 3
    Considering that `DELETE` operates on whole rows rather than columns, specifying *anything* between the `DELETE` and `FROM` keywords makes no sense. – onedaywhen Apr 11 '11 at 13:01

4 Answers4

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

Thomas
  • 63,911
  • 12
  • 95
  • 141
  • 5
    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. – David-W-Fenton Apr 09 '11 at 22:44
  • 2
    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) – Stefan Sep 04 '12 at 19:42
  • @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. – Thomas Sep 04 '12 at 20:12
  • This query can be simplified by removing '= True' redundant comparison. – Mariusz Bialobrzeski Jan 23 '18 at 17:09
  • 1
    In theory...yes. However, I seem to remember old versions of Access effectively requiring the explicit comparison to a boolean constant. – Thomas Jan 24 '18 at 19:48
  • 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. – ashleedawg Mar 27 '18 at 15:55
3

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

MarcusFey
  • 31
  • 1
0
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)
Sim
  • 4,199
  • 4
  • 39
  • 77
user7047561
  • 1
  • Delete from tblA where id in (Select id from tblB) – user7047561 Oct 20 '16 at 11:49
  • for join table: – user7047561 Oct 21 '16 at 03:05
  • 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); – user7047561 Oct 21 '16 at 05:09
  • In response to the comment above, be aware that MS Access does not support Temporary tables. – Dai Dec 23 '20 at 11:08
0

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

Vid
  • 1