SQL aggregation updates for some but not others-CodePudding

I am running this query which should take the sum of an amount from a table and if it <= 0, update the status of a different table from Active to Deactive. The query updates some values but not others. I have isolated to one observation where there are 3 payments that total 0 where it does not work.(123456789) What could be happening here? I am using sql query in Microsoft Access. Thank you.

UPDATE tbl_MASTER INNER JOIN tbl_Payments ON tbl_MASTER.DeviceID = tbl_Payments.DeviceID SET tbl_MASTER.ActiveDeactive = "DeActive"
WHERE tbl_Payments.Amount=(SELECT SUM(tbl_Payments.Amount) <= 0 FROM tbl_Payments) AND tbl__MASTER = '123456789';

CodePudding user response：

Your query doesn't really make a lot of sense, to be honest. Where you have tbl_Payments.Amount=(SELECT SUM(tbl_Payments.Amount) <= 0 FROM tbl_Payments), that sub-query will just be summing up the "Amount" of every record in the table, regardless of which DeviceID. Plus, you're looking for one record in tbl_Payments table where the Amount = the sum of all of the Amounts in tbl_Payments??

I'd suggest that your query probably needs to be something more like this:

UPDATE tbl_MASTER SET tbl_MASTER.ActiveDeactive = "DeActive"
WHERE (SELECT SUM(tbl_Payments.Amount) FROM tbl_Payments WHERE tbl_Payments.DeviceID = tbl_MASTER.DeviceID) <= 0 AND tbl__MASTER = '123456789';

CodePudding user response：

Currently, the subquery does not correlate specific IDs to outer query and also you specify <= 0 inside subquery's SELECT clause. Consider adjusting for IN clause with logic in a conditional HAVING and use table aliases to distinguish same named tables.

UPDATE tbl_MASTER AS m
INNER JOIN tbl_Payments AS p 
   ON m.DeviceID = p.DeviceID 
SET m.ActiveDeactive = 'DeActive'
WHERE sub_p.DeviceID IN (
    SELECT sub_p.DevideID 
    FROM tbl_Payments AS sub_p
    GROUP BY sub_p.DeviceID
    HAVING SUM(sub_p.Amount) <= 0
)