Query Access Update with subquery-CodePudding

I have an Access DB which contains this Fields:

ID (autonumber)
IDArr (numeric)
Importo (Decimal)
Pv (numeric)
Closed (boolean)

I want to set the Closed field to true if sum of Importo is = 0 grouping by IDArr field and I have tried with this query:

UPDATE ln
SET    closed = true
WHERE  Val(idarr) = EXISTS (SELECT idarr
                            FROM   ln
                            WHERE  Val(pv) > 0
                                   AND chiuso = false
                            GROUP  BY idarrivo
                            HAVING SUM(importo) = 0
                            ORDER  BY idarr)

Result is 0 fields. However, if I run this query separately like this:

SELECT idarr
FROM   ln
WHERE  Val(pv) > 0
       AND chiuso = false
GROUP  BY idarrivo
HAVING SUM(importo) = 0
ORDER  BY idarr

I obtain a correct result showing a set of record. Who can help me? Thanks in advance.

CodePudding user response：

if as you say, the inner query is correct, it sounds like you want to do something like this (I can see no reason whatever that you are trying to use exists in your query, that is not going to work at all - remember that exists evaluates to true or false, but you want the idarr values in your subquery, not just a true or false as to whether or not they exist):

update LN 
set Closed = true
where 
    IdArr in (
        select idarr from LN 
        where val(PV) > 0 
        and chiuso = false 
        group by idarrivo  
        having sum(importo) = 0)

CodePudding user response：

you are using the EXISTS wrong and when you uise it like beow it is much fatser than the IN clause

UPDATE ln
SET    closed = true
WHERE  EXISTS (SELECT 1
                            FROM   ln As ln2
                            WHERE  Val(pv) > 0
                                   AND chiuso = false
                            AND ln2.idarr = ln.idarr)