有没有办法在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.

(在

(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屋！