根据同一表中的值访问联接中的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屋！