访问2k / 2003：使用WHERE EXISTS进行删除查询 [英] Access 2k/2003: Touble with delete query using WHERE EXISTS
问题描述
我有一个表，我想根据其他表中的
数据删除特定记录。我对Access''97更熟悉，但现在使用2003年是
，但数据库是2000格式。在''97，我想我/ b $ b可以很容易地使用连接完成这个，但我一直得到无法从指定的表格中删除
错误。一些谷歌搜索有
表示我需要使用子查询。经过多次尝试使用
不同的方法失败后，我终于做了几个上游查询
给我一张我要从表中删除的记录列表，并且
该查询的输出只包含表格的四个关键字段
我要从中删除记录。以下是我的询问：
DELETE FROM FilterJobGroupTally
WHERE EXISTS
（SELECT *
FROM qryFilterDeleteLastPre
WHERE [FilterJobGroupTally]。[JobID] = [qryFilterDeleteLastPre]。[JobID]
AND [FilterJobGroupTally]。[FilterJobGroupID] =
[qryFilterDeleteLastPre]。[FilterJobGroupID] AND
[FilterJobGroupTally]。[FilterTypeID] =
[qryFilterDeleteLastPre]。[FilterTypeID] AND
[FilterJobGroupTally ]。[FilterSizeID] =
[qryFilterDeleteLastPre]。[FilterSizeID]）;
当我运行它时，它会尝试删除
FilterJobGroupTally，而不仅仅是子查询中存在的那些
qryFilterDeleteLastPre。我做错了什么？
谢谢！
I have a table that I want to delete specific records from based on
data in other tables. I''m more familiar with Access ''97, but am now
using 2003, but the database is in 2000 format. In ''97, I think I
could have easily done this using joins, but I kept getting "could not
delete from specified tables" errors. Some google searching has
indicated I need to use a subquery. After many failed attempts with
different approaches, I finally did a couple of upstream queries to
give me the exast list of records I want to delete from the table, and
the output of that query only contains the four key fields of the table
I want to delete records from. Following is my query:
DELETE FROM FilterJobGroupTally
WHERE EXISTS
(SELECT *
FROM qryFilterDeleteLastPre
WHERE [FilterJobGroupTally].[JobID] = [qryFilterDeleteLastPre].[JobID]
AND [FilterJobGroupTally].[FilterJobGroupID] =
[qryFilterDeleteLastPre].[FilterJobGroupID] AND
[FilterJobGroupTally].[FilterTypeID] =
[qryFilterDeleteLastPre].[FilterTypeID] AND
[FilterJobGroupTally].[FilterSizeID] =
[qryFilterDeleteLastPre].[FilterSizeID]);
When I run this, it tries to delete ALL the records in
FilterJobGroupTally, not just the ones that exist in the subquery
qryFilterDeleteLastPre. What am I doing wrong?
Thanks!
推荐答案
试用Microsoft知识库文章：
http://support.microsoft.com /？id = 207761
-
HTH
Van T. Dinh
MVP（访问）
" Darin" <去**** @ darincline.com>在消息中写道
news：11 ********************* @ z14g2000cwz.googlegro ups.com ...
Try the Microsoft Knowledge Base article:
http://support.microsoft.com/?id=207761
--
HTH
Van T. Dinh
MVP (Access)
"Darin" <go****@darincline.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
我有一个表，我想根据其他表中的
数据删除特定记录。我对Access''97更熟悉，但现在使用2003，但数据库是2000格式。在''97，我认为我可以使用连接轻松完成此操作，但我一直在无法从指定的表中删除。错误。一些谷歌搜索已经表明我需要使用子查询。经过多次尝试不同的方法失败后，我终于做了几个上游查询，给我一张我要从表中删除的记录列表，以及该查询的输出。只包含表格的四个关键字段
我想从中删除记录。以下是我的查询：
DELETE FROM FilterJobGroupTally
WHERE EXISTS
（SELECT *
FROM qryFilterDeleteLastPre
WHERE [FilterJobGroupTally]。[JobID] = [qryFilterDeleteLastPre ]。[JobID]
AND [FilterJobGroupTally]。[FilterJobGroupID] =
[qryFilterDeleteLastPre]。[FilterJobGroupID] AND
[FilterJobGroupTally]。[FilterTypeID] =
[qryFilterDeleteLastPre]。 [FilterTypeID] AND
[FilterJobGroupTally]。[FilterSizeID] =
[qryFilterDeleteLastPre]。[FilterSizeID]）;
当我运行它时，它会尝试删除所有记录
FilterJobGroupTally，而不仅仅是子查询中存在的那些
qryFilterDeleteLastPre。我做错了什么？
谢谢！
I have a table that I want to delete specific records from based on
data in other tables. I''m more familiar with Access ''97, but am now
using 2003, but the database is in 2000 format. In ''97, I think I
could have easily done this using joins, but I kept getting "could not
delete from specified tables" errors. Some google searching has
indicated I need to use a subquery. After many failed attempts with
different approaches, I finally did a couple of upstream queries to
give me the exast list of records I want to delete from the table, and
the output of that query only contains the four key fields of the table
I want to delete records from. Following is my query:
DELETE FROM FilterJobGroupTally
WHERE EXISTS
(SELECT *
FROM qryFilterDeleteLastPre
WHERE [FilterJobGroupTally].[JobID] = [qryFilterDeleteLastPre].[JobID]
AND [FilterJobGroupTally].[FilterJobGroupID] =
[qryFilterDeleteLastPre].[FilterJobGroupID] AND
[FilterJobGroupTally].[FilterTypeID] =
[qryFilterDeleteLastPre].[FilterTypeID] AND
[FilterJobGroupTally].[FilterSizeID] =
[qryFilterDeleteLastPre].[FilterSizeID]);
When I run this, it tries to delete ALL the records in
FilterJobGroupTally, not just the ones that exist in the subquery
qryFilterDeleteLastPre. What am I doing wrong?
Thanks!
谢谢......我确实看到了，并尝试过使用DISTINCT各种形式的各种形式的各种形式的查询，以实现这一点，但仍然没有运气。它没有帮助以前的查询，它说无法从
指定的表格中删除，并且它对我这个最新版本没有帮助
张贴在这里。有或没有，结果是相同的。它希望
删除表中的每条记录。我现在至少乐观了，因为我已经可以删除记录，现在我只需要删除记录，现在我只需要它就可以了。
只删除了我想要的！昨天，当它绝对没有
的方式甚至会尝试删除数据时，我即将走向
糟糕的数据库设计。路由，只是在
表中添加一个是/否字段，并带有更新查询，以便将我要删除的记录设为
" yes"和a随后的SIMPLE删除查询删除那些
是的。我真的不想走那条路。 ;-)
Thanks... I did see that, and have tried using DISTINCT in various
queries of various forms to get this to happen, and still no luck. It
didn''t help the previous queries where it said "could not delete from
specified tables", and it doesn''t help this latest version that I
posted here. With or without, the results are the same. It wants to
delete every record in the table. I''m at least optimistic now that I''m
to a point where it CAN delete records, now I just have to get it so it
only deletes the ones I want it to! Yesterday, when it absolutely no
way would even try to delete data, I was on the verge of going the
"poor database design" route, and just adding a yes/no field to the
table, with an update query to make those records I want to delete as
"yes", and a subsequent SIMPLE delete query to delete the ones that are
yes. I really don''t want to have to go that route though. ;-)
你知道，我从未使用Exists。
为什么不直接使用in caluse？
DELETE FROM FilterJobGroupTally
WHERE JobID in（12,13,14）
你可以替换 ;在"部分选择查询如下：
DELETE FROM FilterJobGroupTally WHERE JobID in
（SELECT JODID FROM qryFilterDeleteLastPre
WHERE bla bla bla）
所以，得到in查询单独工作，重新调整你想要的reocrds。
我注意到：
[FilterJobGroupTally]。[JobID] = [qryFilterDeleteLastPre]。[JobID]
您在该子查询中有两个不同的表，但我没有看到第二个表中的连接或
表。您需要在
连接中指定第二个表，或在where子句中指定隐含连接。正如我所提到的那样，单独使用查询
，然后将该查询放在上面的in中。条款....
上述方法是存在的一个很好的替代方案....
-
Albert D. Kallal（访问MVP）
加拿大艾伯塔省埃德蒙顿
pl ***************** @ msn.com
http://www.members.shaw.ca/AlbertKallal
you know, i never used Exists.
Why not just use the in caluse?
DELETE FROM FilterJobGroupTally
WHERE JobID in (12, 13, 14)
You can replace the "in" part with a select query as follows:
DELETE FROM FilterJobGroupTally WHERE JobID in
(SELECT JodID FROM qryFilterDeleteLastPre
WHERE bla bla bla)
So, get that "in" query working seperately that retun the reocrds you want.
I notice that:
[FilterJobGroupTally].[JobID] = [qryFilterDeleteLastPre].[JobID]
You got two different tables in that sub-query, but I don''t see the join, or
table placed in for the 2nd table. You need to specify that 2nd table in the
join, or a implied join in a where clause. As I mentioned, get the query
working alone, and then put that query in the above "in" clause....
The above approach is a good alternative to exists....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
这篇关于访问2k / 2003：使用WHERE EXISTS进行删除查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
