在 UPDATE 查询中使用 SELECT [英] Use SELECT inside an UPDATE query

问题描述

如何在 Microsoft Access 2007 中使用 SELECT 查询的结果UPDATE 表的字段.

How can I UPDATE a field of a table with the result of a SELECT query in Microsoft Access 2007.

这是选择查询:

SELECT Min(TAX.Tax_Code) AS MinOfTax_Code
FROM TAX, FUNCTIONS

WHERE (((FUNCTIONS.Func_Pure)<=[Tax_ToPrice]) AND ((FUNCTIONS.Func_Year)=[Tax_Year]))

GROUP BY FUNCTIONS.Func_ID;

这里是更新查询:

UPDATE FUNCTIONS

 SET FUNCTIONS.Func_TaxRef = [Result of Select query]

推荐答案

嗯，看起来 Access 不能在 UPDATE 查询中进行聚合.但它可以在 SELECT 查询中进行聚合.因此，使用如下定义创建一个查询:

Well, it looks like Access can't do aggregates in UPDATE queries. But it can do aggregates in SELECT queries. So create a query with a definition like:

SELECT func_id, min(tax_code) as MinOfTax_Code
FROM Functions
INNER JOIN Tax 
ON (Functions.Func_Year = Tax.Tax_Year) 
AND (Functions.Func_Pure <= Tax.Tax_ToPrice) 
GROUP BY Func_Id

并将其保存为 YourQuery.现在我们必须解决另一个访问限制.UPDATE 查询不能对查询进行操作，但可以对多个表进行操作.因此，让我们将查询变成带有 Make Table 查询的表:

And save it as YourQuery. Now we have to work around another Access restriction. UPDATE queries can't operate on queries, but they can operate on multiple tables. So let's turn the query into a table with a Make Table query:

SELECT YourQuery.* 
INTO MinOfTax_Code
FROM YourQuery

这会将视图的内容存储在名为 MinOfTax_Code 的表中.现在您可以执行 UPDATE 查询:

This stores the content of the view in a table called MinOfTax_Code. Now you can do an UPDATE query:

UPDATE MinOfTax_Code 
INNER JOIN Functions ON MinOfTax_Code.func_id = Functions.Func_ID 
SET Functions.Func_TaxRef = [MinOfTax_Code].[MinOfTax_Code]

在 Access 中执行 SQL 有点牵强，我会为您的项目研究 Sql Server Express Edition！

Doing SQL in Access is a bit of a stretch, I'd look into Sql Server Express Edition for your project!

这篇关于在 UPDATE 查询中使用 SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！