根据同一表中的值访问 SQL 更新联接中的一个表 [英] Access SQL Update One Table In Join Based on Value in Same Table

问题描述

我在 Access 中有一个名为tempSpring_ASN"的表，其中包含以下字段(以及其他字段):

I have a table in Access called "tempSpring_ASN", with the following fields (among others):

SHP_CUSTOM_5(自动编号)

SHP_CUSTOM_5 (AutoNumber)

RECORD_TYPE(文本)

RECORD_TYPE (Text)

PO_NUM(文本).

PO_NUM (Text).

我需要改变RECORD_TYPE的值，如果PO_NUM和上一条记录中的PO_NUM一样，那么RECORD_TYPE应该是LIN"，否则(或者如果是第一条记录)，RECORD_TYPE应该是HDR".

I need to change the value of RECORD_TYPE, such that if PO_NUM is the same as PO_NUM in the previous record, then RECORD_TYPE should be "LIN", otherwise (or if it's the first record), RECORD_TYPE should be "HDR".

我创建了以下查询来获取 RECORD_TYPE 的正确新值:

I've created the following query to get the correct new value for RECORD_TYPE:

SELECT TOP 1 t1.SHP_CUSTOM_5,
    t1.PO_NUM AS CurrentValue,
    NULL AS PreviousValue,
    "HDR" AS RECORD_TYPE
FROM tempSpring_ASN AS t1
ORDER BY t1.SHP_CUSTOM_5
UNION ALL
SELECT t1.SHP_CUSTOM_5,
    t1.PO_NUM AS CurrentValue,
    t2.PO_NUM AS PreviousValue,
    IIf([CurrentValue]=[PreviousValue],'LIN','HDR') AS RECORD_TYPE
FROM tempSpring_ASN AS t1,
    tempSpring_ASN AS t2
WHERE t1.SHP_CUSTOM_5 = t2.SHP_CUSTOM_5 + 1
ORDER BY t1.SHP_CUSTOM_5;

我已将该查询保存为tempSpring_ASN_With_PreviousRow".现在我正在尝试使用它来更新原始 tempSpring_ASN 表，并使用以下查询:

I've saved that query as "tempSpring_ASN_With_PreviousRow". Now I'm trying to use that to update the original tempSpring_ASN table with the following query:

UPDATE tempSpring_ASN INNER JOIN tempSpring_ASN_With_PreviousRow ON tempSpring_ASN.SHP_CUSTOM_5 = tempSpring_ASN_With_PreviousRow.SHP_CUSTOM_5 SET tempSpring_ASN.RECORD_TYPE = [tempSpring_ASN_With_PreviousRow].[RECORD_TYPE];

但我得到了，操作必须使用可更新的查询."我不确定是因为我试图更新连接中的一个表，还是因为我试图根据同一个表中的值更新表，或者是由于其他原因.无论如何，我正在寻找可行的方法.

but I'm getting, "Operation must use an updateable query." I'm not sure if it's because I'm trying to update one table in a join, or because I'm trying to update a table based on a value in that same table, or due to something else. Regardless, I'm looking for something which works.

谢谢！

更新(没有双关语):

我尝试了以下更新查询:

I've tried the following update query:

UPDATE tempSpring_ASN INNER JOIN Table5 ON tempSpring_ASN.SHP_CUSTOM_5 = Table5.SHP_CUSTOM_5 SET tempSpring_ASN.RECORD_TYPE = "zzz";

而且效果很好.结果是 tempSpring_ASN 已更新，但 Table5 没有.显然，如果在一条 SQL 语句中连接了两个表，即使您对一个表运行更新，它仍然不会尝试更新连接中的另一个表.既然如此，我不确定为什么我原来的更新查询不起作用.我知道 tempSpring_ASN_With_PreviousRow 不可更新，因为它是一个 UNION 查询，但我没有尝试更新它.相反，我正在尝试更新 tempSpring_ASN——连接中的另一个表，是可更新的.

and it worked fine. The result was that tempSpring_ASN was updated, but Table5 wasn't. Apparently, if two tables are joined in a SQL statement, even if you run an update on one table, it still won't try to update the other table in the join. That being the case, I'm not sure why my original update query won't work. I know that tempSpring_ASN_With_PreviousRow isn't updateable, because it's a UNION query, but I'm not trying to update it. Rather I'm trying to update tempSpring_ASN--the other table in the join, which is updateable.

更新 2:然后我尝试使用如下相关子查询:

Update 2: Then I tried using a correlated subquery as follows:

UPDATE tempSpring_ASN AS t
SET t.RECORD_TYPE = (
        SELECT RECORD_TYPE
        FROM (
            SELECT TOP 1 t1.SHP_CUSTOM_5,
                t1.PO_NUM AS CurrentValue,
                NULL AS PreviousValue,
                "HDR" AS RECORD_TYPE
            FROM tempSpring_ASN AS t1
            ORDER BY t1.SHP_CUSTOM_5
            UNION ALL
            SELECT t1.SHP_CUSTOM_5,
                t1.PO_NUM AS CurrentValue,
                t2.PO_NUM AS PreviousValue,
                IIf([CurrentValue] = [PreviousValue], 'LIN', 'HDR') AS RECORD_TYPE
            FROM tempSpring_ASN AS t1,
                tempSpring_ASN AS t2
            WHERE t1.SHP_CUSTOM_5 = t2.SHP_CUSTOM_5 + 1
            ORDER BY t1.SHP_CUSTOM_5
            )
        WHERE SHP_CUSTOM_5 = t.SHP_CUSTOM_5
        );

但我仍然收到操作必须使用可更新查询".

but I'm still getting "Operation must use an updateable query."

更新 3:

我认为错误是由于我使用联合查询造成的.为了隔离问题，我尝试了以下方法(这不会给我想要的结果，但会帮助我诊断问题.):

I believe that the error is due to the fact that I'm using a union query. To isolate the problem, I've tried the following (which would NOT give me the result I want, but would help me diagnose the problem.):

UPDATE tempSpring_ASN AS t
SET t.RECORD_TYPE = (
        SELECT TOP 1 RECORD_TYPE
        FROM tempSpring_ASN_With_PreviousRow
        );

它给了我同样的错误.所以现在的问题变成了，为什么我不能使用联合查询输出的单个值来设置记录集中的值?

It gave me the same error. So the question now becomes, why can't I use a single value of an output of a union query to set a value in a recordset?

推荐答案

我也在你的新问题中发布了这个答案.

I have also posted this answer within your new question.

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).

祝你好运！

这篇关于根据同一表中的值访问 SQL 更新联接中的一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！