如何在MS-Access中创建相关的更新子查询? [英] How to create a correlated update subquery in MS-Access?

问题描述

我正在规范一些表，并且已经向名为Exams的表中添加了代理主键，该表具有考试标题.

I'm in the process of normalizing a few tables and I've added a surrogate primary key to a table called Exams which holds exam titles.

以前，子表只将考试的全名用作FK条目.

Previously, the child tables would just use the entire name of the exam as the FK entry.

现在，我已经向表中添加了自动编号字段，我想更新使用它的条目，例如问题所在的表，因为有上千个问题.

Now that I've added a autonumbered field to the table, I want to update the entries that use it such as the table where the questions are from as there are over a thousand of them.

每次找到名称时都要经过修改的更新来进行每次考试，这需要一段时间，因此我决定在UPDATE查询中编写一个相关的子查询.查询看起来像这样:

Going through each exam with a modified update once I find the name each time would take a while, so I decided to write a correlated subquery in a UPDATE query. The query looks like this:

UPDATE tblExamQuestion
SET ExamID = (SELECT ExamID FROM tblExam WHERE ExamName = tblExamQuestion.ExamName);

不幸的是，一旦我编写了子查询部分，Access就会拒绝给我Run选项，而只显示 Design视图，数据表视图和 SQL查看该查询.但是，它仍在对象资源管理器中显示更新查询图标.

Unfortunately, once I write the subquery portion, Access refuses to give me the Run option and just displays the Design View, Datasheet View and SQL View for that query. However, it still displays the Update Query icon in the object explorer.

是否有适当的方式编写此代码，以使Access不会感到不安?

Is there a proper way to write this so that Access doesn't get upset?

使用:具有Access 2003 MDB数据库的Access 2007.

推荐答案

为什么不呢?

UPDATE tblExamQuestion
INNER JOIN tblExam 
ON tblExam.ExamName = tblExamQuestion.ExamName
SET tblExamQuestion.ExamID = tblExam.ExamID 

这篇关于如何在MS-Access中创建相关的更新子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！