MS ACCESS删除查询语法并结合内部联接问题 [英] MS ACCESS delete query syntax combined with inner join problems

问题描述

我无法使以下删除查询正常工作(MS Access 2007)

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

查询的目的是有效地删除重复的条目. DataId是表的单个主键.通过比较字段MeasTime和JobId确定重复的条目.

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返回消息指定包含要删除的记录的表.任何帮助将不胜感激.

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

推荐答案

当您将两个表连接在一起时，您可能会对要删除的内容产生一些歧义，例如，您可能在第一个表中的行具有很多匹配项在第二个表中.连接后，连接表中第一个表中的行将有很多副本，当您尝试使用连接表中的第一个表删除条目时，这会混淆Access.

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.

有两种解决方案:

1)使用DistinctRow:这将停止上一个问题，因为这将意味着联接表中的每一行都是唯一的并避免了上一个问题:

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)使用子查询:尽管子查询可能很慢，但这可以防止结果出现歧义，并避免您必须删除重复的行:

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 ) 

这篇关于MS ACCESS删除查询语法并结合内部联接问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！