CODEWITHSUNDEEP
sqlms-access
user3034139
user3034139

Reputation: 21

UPDATE with aggregate function and SELECT

I'm trying to write a SQL instruction which update my database stock product quantity related to aggregate number of all delivers and orders.

UPDATE tbl_Product 
SET tbl_Product.Quantity = Q.Quantity
FROM 
    tbl_Product 
    INNER JOIN 
    (
        SELECT tbl_Order.IdProduct, 
        SUM(tbl_Delivery.Quantity)-SUM(tbl_Order.Quantity) AS Quantity 
        FROM 
            tbl_Order
            INNER JOIN 
            tbl_Delivery 
                ON tbl_Order.IdProduct = tbl_Delivery.IdProduct 
        GROUP BY tbl_Order.IdProduct
    ) as Q 
        ON Q.IdProduct = tbl_Product.IdProduct;

Got a syntax error.

Upvotes: 2

Views: 1654

Answers (4)

HansUp
HansUp

Reputation: 97111

Access can be finicky with an UPDATE which uses a join. In certain situations, it may complain that the query is read-only. And since your UPDATE uses a join with a GROUP BY, that may happen here.

If it does, you can use DSum so Access will consider your query to be editable. If IdProduct is numeric data type use this ...

UPDATE tbl_Product AS p 
SET p.Quantity = 
    DSum("Quantity", "tbl_Delivery", "IdProduct=" & p.IdProduct)
    - DSum("Quantity", "tbl_Order", "IdProduct=" & p.IdProduct)

But if it's text type, enclose those values in quotes for the third option to DSum.

UPDATE tbl_Product AS p 
SET p.Quantity = 
    DSum("Quantity", "tbl_Delivery", "IdProduct='" & p.IdProduct & "'")
    - DSum("Quantity", "tbl_Order", "IdProduct='" & p.IdProduct & "'")

Upvotes: 3

Gord Thompson
Gord Thompson

Reputation: 123664

Access SQL does not support the syntax you are trying to use. The query

UPDATE foo
SET foo.thing = bar.thing
FROM
    foo
    INNER JOIN
    bar
        ON foo.ID = bar.ID

results in "Syntax error (missing operator) in query expression."

The corresponding query in Access SQL would be

UPDATE 
    foo 
    INNER JOIN 
    bar 
        ON foo.ID = bar.ID 
SET foo.thing = bar.thing

Upvotes: 1

Mihai
Mihai

Reputation: 26784

UPDATE tbl_Product  
    INNER JOIN 
    (
        SELECT tbl_Order.IdProduct, 
        SUM(tbl_Delivery.Quantity)-SUM(tbl_Order.Quantity) AS Quantity 
        FROM 
            tbl_Order
            INNER JOIN 
            tbl_Delivery 
                ON tbl_Order.IdProduct = tbl_Delivery.IdProduct 
        GROUP BY tbl_Order.IdProduct
    ) as Q 
        ON Q.IdProduct = tbl_Product.IdProduct   
        SET tbl_Product.Quantity = Q.Quantity

Remove FROM move the SET last.

Upvotes: 2

nycdan
nycdan

Reputation: 2839

Try assigning a nickname to the table you are updating. Here is your code with the subquery abstracted. Does this help?

UPDATE P
SET P.Quantity = Q.Quantity
FROM tbl_Product P
INNER JOIN (...) as Q 
ON Q.IdProduct = P.IdProduct;

Upvotes: 0

Related Questions