从嵌套查询更新MS Access表

My SQL is very rusty. Am trying to update a counter in a row in a table with a COUNT(*) from that same table in the form of a nested query. The SQL is below:

我的SQL非常生疏。我试图以嵌套查询的形式更新同一个表中COUNT（*）的表中一行的计数器。 SQL如下：

UPDATE DWInvoiceHeader AS A
SET A.InvCount = (Select Count(B.HIINV) From DWInvoiceHeader AS B 
                  WHERE (B.HIVENT = '0') 
                  Group By B.HIINV 
                  Order By B.HIINV)
WHERE (A.HIVENT = '0');

The rows look like:

行看起来像：

HIINV1.......Seq1.....InvCount   - want InvCount to be 3
HIINV1.......Seq2.....InvCount   - want InvCount to be 3
HIINV1.......Seq3.....InvCount   - want InvCount to be 3
HIINV2.......Seq1.....InvCount   - want InvCount to be 2 
HIINV2.......Seq2.....Invcount   - want InvCount to be 2
.
.
.
HIINVn.......Seq1.....InvCount   - want InvCount to be 1

The SQL above gives me the message "Operation must be an updateable query".

上面的SQL给出了消息“操作必须是可更新的查询”。

Any ideas ?

有任何想法吗？

2 个解决方案

#1

UPDATE DWInvoiceHeader AS A
SET A.InvCount = 
(
    Select Count(B.HIINV) 
        From DWInvoiceHeader AS B 
        WHERE (B.HIVENT = '0') AND (A.HIINV = B.HIINV)
        Group By B.HIINV 
)
WHERE (A.HIVENT = '0');

#2

You could use the Domain Function Dcount in a way similar to:

您可以使用类似于以下的方式使用域功能Dcount：

UPDATE test
SET InvCount = Dcount("HIVENT","test","[HIVENT]='0'")

If you want to use a generic approach then I would suggest to store the counts in a working table and then update your table joining the working table.

如果您想使用通用方法，那么我建议将计数存储在工作表中，然后更新加入工作表的表。

    Select B.HIINV,Count(B.HIINV) as InvCount 
    into WorkingTbl
    From DWInvoiceHeader AS B 
    WHERE (B.HIVENT = '0') AND (A.HIINV = B.HIINV)
    Group By B.HIINV 

   Update   DWInvoiceHeader  A 
   Inner Join WorkingTbl b 
   on a.HIINV=b.HIINV 
   Set A.InvCount=b.InvCount

#1

UPDATE DWInvoiceHeader AS A
SET A.InvCount = 
(
    Select Count(B.HIINV) 
        From DWInvoiceHeader AS B 
        WHERE (B.HIVENT = '0') AND (A.HIINV = B.HIINV)
        Group By B.HIINV 
)
WHERE (A.HIVENT = '0');

#2