有没有办法在 UPDATE 查询中包含不可更新的查询? [英] Is there a way to include a query that is non updateable in an UPDATE query?

问题描述

对于以下查询:

UPDATE tempSpring_ASN AS t
SET t.RECORD_TYPE = (
        SELECT TOP 1 RECORD_TYPE
        FROM (
            SELECT "A" AS RECORD_TYPE
            FROM TABLE5
            UNION ALL
            SELECT "B" AS RECORD_TYPE
            FROM TABLE5
            )
        );

我得到了，操作必须使用可更新的查询."我不明白.我不是要更新联合查询.我只是想用联合查询的输出(单个值)更新一个可更新的记录集.

I'm getting, "Operation must use an updateable query." I don't understand. I'm not trying to update a union query. I'm just trying to update an otherwise updatable recordset with the output (single value) of a union query.

(Access SQL Update One Table In Join Based on Value in Same Table(下面也提供)不适用于这种情况，与本页顶部的说明相反.)

(The solution provided at Access SQL Update One Table In Join Based on Value in Same Table (which is also provided below) does not work for this situation, contrary to what is indicated on the top of this page.)

推荐答案

这个问题参考了之前的一个问题，这里贴的数据和代码示例:

This question is a reference to a previous question, data and code examples posted here:

访问 SQL 更新基于同一表中的值连接一个表

AYS，

在 Access 中，需要对表运行更新查询.由于 UNION 查询是多组记录的组合，结果集不再是一个表，也不能成为 Update 查询的对象，因为结果集中的记录不再与任何一个特定表唯一标识(甚至如果理论上可以的话).访问被硬编码以将每个 UNION 查询视为只读，这在有多个基础表时是有意义的.还有许多其他条件(例如 SELECT 语句中的子查询)也会触发此条件.

In Access, an Update query needs to be run on a table. As a UNION query is a combination of multiple sets of records, the result set is no longer a table, and cannot be the object of an Update query as the records in the result set are no longer uniquely identified with any one particular table (even if they theoretically could be). Access is hard-coded to treat every UNION query as read-only, which makes sense when there are multiple underlying tables. There are a number of other conditions (such as a sub-query in the SELECT statement) that also trigger this condition.

这样想:如果您没有使用 TOP 1 并且您的 UNION 查询返回了多个结果，那么 JET 如何知道将哪个结果应用于您的表中的唯一记录?因此，JET 对所有此类情况一视同仁.

Think if it this way: if you were not using TOP 1 and your UNION query returned multiple results, how would JET know which result to apply to the unique record in your table? As such, JET treats all such cases the same.

不幸的是，即使所有数据都来自同一个表，情况也是如此.在这种情况下，很可能 JET 优化器不够聪明，无法意识到这种情况并以不使用 UNION 的方式重新表述查询.

Unfortunately, this is the case even when all of the data is being derived from the same table. In this case, it is likely that the JET optimizer is simply not smart enough to realize that this is the case and re-phrase the query in a manner that does not use UNION.

在这种情况下，您仍然可以通过重新声明查询以使所有内容都引用您的基表来获得所需的内容.例如，您可以使用以下语句作为 SELECT 查询来获取上一条 SHP_CUSTOM_5 记录的 PO_NUM 值:

In this case, you can still get what you want by re-stating your query in such a way that everything references your base table. For example, you can use the following as a SELECT query to get the PO_NUM value of the previous SHP_CUSTOM_5 record:

SELECT
t1.SHP_CUSTOM_5
, t1.PO_NUM
, t1.SHP_CUSTOM_5 -1 AS PREV_RECORD

, (SELECT
t2.PO_NUM
FROM
tempSpring_ASN As t2
WHERE
t2.SHP_CUSTOM_5 = (t1.SHP_CUSTOM_5 -1)
) AS PREV_PO

FROM
tempSpring_ASN AS t1
;

然后，您可以将其表述为更新查询，如下所示，以执行LIN"更新:

You can then phrase this as an Update query as follows in order to perform the "LIN" updates:

UPDATE
tempSpring_ASN AS t1 

SET 
t1.RECORD_TYPE = "LIN"

WHERE
t1.PO_NUM=

(
SELECT 
t2.PO_NUM

FROM
tempSpring_ASN As t2

WHERE
t2.SHP_CUSTOM_5 = (t1.SHP_CUSTOM_5 -1)
)
;

这段代码在我使用虚拟数据运行的测试中是成功的.

This code was successful in the tests I ran with dummy data.

关于您的HDR"更新，您实际上是在执行两个单独的更新.1) 如果 PO_NUM 与上一条记录的 PO_NUM 匹配，则将 RECORD_TYPE 设置为LIN"2)如果是第一条记录，设置RECORD_TYPE为HDR"

Regarding your "HDR" updates, your are really performing two separate updates. 1) If the PO_NUM matches the previous record's PO_NUM, set RECORD_TYPE to "LIN" 2) If it is the first record, set RECORD_TYPE to "HDR"

我不清楚为什么在一个查询中执行这些操作会有好处.我建议使用您在原始 SELECT 查询示例中使用的 SHP_CUSTOM_5 方法的TOP 1"执行 HDR 更新，因为这将是一个相对简单的 UPDATE 查询.可以在 Update 查询中使用 IIF()，但我不知道您会从所需的额外时间和复杂性中获得什么额外好处(它很可能只是可读性低得多).

It is not clear to me why there would be a benefit to performing these actions within one query. I would recommend performing the HDR update using the "TOP 1" by SHP_CUSTOM_5 method you used in your original SELECT query example, as this will be a relatively simple UPDATE query. It is possible to use IIF() within an Update query, but I do not know what additional benefit you would gain from the additional time and complexity that would be required (it would most likely only be much less readable).

祝你好运！

这篇关于有没有办法在 UPDATE 查询中包含不可更新的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！