SQL 在 UPDATE 查询中使用 SELECT

Use SELECT inside an UPDATE query

提问by mammadalius

How can I UPDATEa field of a table with the result of a SELECTquery in Microsoft Access 2007.

如何UPDATE使用SELECTMicrosoft Access 2007中的查询结果创建表的字段。

Here's the Select Query:

这是选择查询：

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;

And here's the Update Query:

这是更新查询：

UPDATE FUNCTIONS

 SET FUNCTIONS.Func_TaxRef = [Result of Select query]

回答by Andomar

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:

好吧，看起来 Access 不能在 UPDATE 查询中进行聚合。但它可以在 SELECT 查询中进行聚合。因此，创建一个具有如下定义的查询：

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

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:

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

SELECT YourQuery.* 
INTO MinOfTax_Code
FROM YourQuery

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

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

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

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

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

回答by ewbi

I wrote about some of the limitations of correlated subqueriesin Access/JET SQL a while back, and noted the syntax for joining multiple tables for SQL UPDATEs. Based on that info and some quick testing, I don't believe there's any way to do what you want with Access/JET in a single SQL UPDATE statement. If you could, the statement would read something like this:

不久前，我写了有关Access/JET SQL中相关子查询的一些限制，并指出了为 SQL UPDATE 连接多个表的语法。根据该信息和一些快速测试，我认为没有任何方法可以在单个 SQL UPDATE 语句中使用 Access/JET 执行您想要的操作。如果可以的话，声明会是这样的：

UPDATE FUNCTIONS A
INNER JOIN (
  SELECT AA.Func_ID, Min(BB.Tax_Code) AS MinOfTax_Code
  FROM TAX BB, FUNCTIONS AA
  WHERE AA.Func_Pure<=BB.Tax_ToPrice AND AA.Func_Year= BB.Tax_Year
  GROUP BY AA.Func_ID
) B 
ON B.Func_ID = A.Func_ID
SET A.Func_TaxRef = B.MinOfTax_Code

Alternatively, Access/JET will sometimes let you get away with saving a subquery as a separate query and then joining it in the UPDATE statement in a more traditional way. So, for instance, if we saved the SELECT subquery above as a separate query named FUNCTIONS_TAX, then the UPDATE statement would be:

或者，Access/JET 有时会让您将子查询另存为单独的查询，然后以更传统的方式将其加入到 UPDATE 语句中。因此，例如，如果我们将上面的 SELECT 子查询保存为名为 FUNCTIONS_TAX 的单独查询，那么 UPDATE 语句将是：

UPDATE FUNCTIONS
INNER JOIN FUNCTIONS_TAX
ON FUNCTIONS.Func_ID = FUNCTIONS_TAX.Func_ID
SET FUNCTIONS.Func_TaxRef = FUNCTIONS_TAX.MinOfTax_Code

However, this still doesn't work.

但是，这仍然不起作用。

I believe the only way you will make this work is to move the selection and aggregation of the minimum Tax_Code value out-of-band. You could do this with a VBA function, or more easily using the Access DLookup function. Save the GROUP BY subquery above to a separate query named FUNCTIONS_TAX and rewrite the UPDATE statement as:

我相信您要完成这项工作的唯一方法是将最小 Tax_Code 值的选择和聚合移到带外。您可以使用 VBA 函数或使用 Access DLookup 函数更轻松地完成此操作。将上面的 GROUP BY 子查询保存到名为 FUNCTIONS_TAX 的单独查询中，并将 UPDATE 语句重写为：

UPDATE FUNCTIONS
SET Func_TaxRef = DLookup(
  "MinOfTax_Code", 
  "FUNCTIONS_TAX", 
  "Func_ID = '" & Func_ID & "'"
)

Note that the DLookup function prevents this query from being used outside of Access, for instance via JET OLEDB. Also, the performance of this approach can be pretty terrible depending on how many rows you're targeting, as the subquery is being executed for each FUNCTIONS row (because, of course, it is no longer correlated, which is the whole point in order for it to work).

请注意，DLookup 函数可防止在 Access 之外使用此查询，例如通过 JET OLEDB。此外，这种方法的性能可能非常糟糕，具体取决于您定位的行数，因为正在为每个 FUNCTIONS 行执行子查询（因为，当然，它不再相关，这是顺序的重点让它工作）。

Good luck!

祝你好运！

回答by Spud_Gasket

I had a similar problem. I wanted to find a string in one column and put that value in another column in the same table. The select statement below finds the text inside the parens.

我有一个类似的问题。我想在一列中找到一个字符串，并将该值放在同一个表的另一列中。下面的 select 语句查找括号内的文本。

When I created the query in Access I selected all fields. On the SQL view for that query, I replaced the mytable.myfield for the field I wanted to have the value from inside the parens with

当我在 Access 中创建查询时，我选择了所有字段。在该查询的 SQL 视图中，我将 mytable.myfield 替换为我想要从括号内获取值的字段

SELECT Left(Right(OtherField,Len(OtherField)-InStr((OtherField),"(")), 
            Len(Right(OtherField,Len(OtherField)-InStr((OtherField),"(")))-1) 

I ran a make table query. The make table query has all the fields with the above substitution and ends with INTO NameofNewTable FROM mytable

我运行了一个制作表查询。make table 查询具有上述替换的所有字段，并以INTO NameofNewTable FROM mytable

回答by Bobort

I did want to add one more answer that utilizes a VBA function, but it does get the job done in one SQL statement. Though, it can be slow.

我确实想再添加一个使用 VBA 函数的答案，但它确实在一个 SQL 语句中完成了工作。尽管如此，它可能会很慢。

UPDATE FUNCTIONS
SET FUNCTIONS.Func_TaxRef = DLookUp("MinOfTax_Code", "SELECT
FUNCTIONS.Func_ID,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;", "FUNCTIONS.Func_ID=" & Func_ID)

回答by sbaer

I know this topic is old, but I thought I could add something to it.

我知道这个话题很旧，但我想我可以添加一些东西。

I could not make an Update with Select query work using SQL in MS Access 2010. I used Tomalak's suggestion to make this work. I had a screenshot, but am apparently too much of a newb on this site to be able to post it.

我无法在 MS Access 2010 中使用 SQL 使用 Select 查询进行更新。我使用 Tomalak 的建议来完成这项工作。我有一个截图，但显然这个网站上的新手太多，无法发布它。

I was able to do this using the Query Design tool, but even as I was looking at a confirmed successful update query, Access was not able to show me the SQL that made it happen. So I could not make this work with SQL code alone.

我可以使用 Query Design 工具完成此操作，但即使在查看已确认的成功更新查询时，Access 也无法向我显示实现它的 SQL。所以我无法单独使用 SQL 代码来完成这项工作。

I created and saved my select query as a separate query. In the Query Design tool, I added the table I'm trying to update the the select query I had saved (I put the unique key in the select query so it had a link between them). Just as Tomalak had suggested, I changed the Query Type to Update. I then just had to choose the fields (and designate the table) I was trying to update. In the "Update To" fields, I typed in the name of the fields from the select query I had brought in.

我创建并保存了我的选择查询作为单独的查询。在查询设计工具中，我添加了我试图更新我保存的选择查询的表（我将唯一键放在选择查询中，因此它们之间有一个链接）。正如 Tomalak 所建议的那样，我将查询类型更改为更新。然后我只需要选择我试图更新的字段（并指定表）。在“更新到”字段中，我输入了我引入的选择查询中的字段名称。

This format was successful and updated the original table.

此格式成功并更新了原始表。

回答by beach

Does this work? Untested but should get the point across.

这行得通吗？未经测试，但应该明白这一点。

UPDATE FUNCTIONS
SET Func_TaxRef = 
(
  SELECT Min(TAX.Tax_Code) AS MinOfTax_Code
  FROM TAX, FUNCTIONS F1
  WHERE F1.Func_Pure <= [Tax_ToPrice]
    AND F1.Func_Year=[Tax_Year]
    AND F1.Func_ID = FUNCTIONS.Func_ID
  GROUP BY F1.Func_ID;
)

Basically for each row in FUNCTIONS, the subquery determines the minimum current tax code and sets FUNCTIONS.Func_TaxRef to that value. This is assuming that FUNCTIONS.Func_ID is a Primary or Unique key.

基本上对于 FUNCTIONS 中的每一行，子查询确定最小当前税码并将 FUNCTIONS.Func_TaxRef 设置为该值。这是假设 FUNCTIONS.Func_ID 是主键或唯一键。