1

I'm having trouble getting the follow delete query to work (ms access 2007)

DELETE 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

The aim of the query is to remove duplicate entries efficiently. DataId is the tables single primary key. Duplicate entries are determined by comparing the fields MeasTime and JobId.

Access returns the message Specify the table containing the records you wish to delete. Any help will be much appreciated.

Joseph King
  • 5,089
  • 1
  • 30
  • 37
  • 1
    Perhaps `delete b.* From T_Value b` you can't delete from two tables at once so you must specify from which to delete. Now the question is do you want to keep the lower dataID or the newer dataId. It will determine if you mean b.* or a.* – xQbert Apr 22 '14 at 19:19
  • I just tried your suggestion and access replied with 'Could not delete from specified tables'. – Joseph King Apr 22 '14 at 19:24
  • Build a select statement such that it contains the records you want to delete using query builder. Then change it from a select to a delete. Look at the SQL there and see what it does. – xQbert Apr 22 '14 at 19:32
  • possible duplicate of [How to delete in MS Access when using JOIN's?](http://stackoverflow.com/questions/5585732/how-to-delete-in-ms-access-when-using-joins) – Bacon Bits Apr 22 '14 at 20:34

1 Answers1

3

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 )
JCollerton
  • 3,227
  • 2
  • 20
  • 25
  • `DistinctRow` is a key word unique to Access SQL. I've only ever seen it used in SELECT statements and never had a use case for it. `Distinct` is very similar and more standard so I use for SELECTs instead. This solution shows how **DistinctRow** has purpose. – Ben Jun 20 '19 at 20:10