SQL聚合更新，但没有其他

发布于 2025-02-05 23:12:48 字数 433 浏览 3 评论 0原文

我正在运行此查询，该查询应该从表中获取金额的总和，如果＆lt; = 0，请更新不同表格的状态，从活动性到停用。查询更新一些值，但没有更新其他值。我已经隔离了一个观察结果，其中有3个付款总计为0的付款。（123456789）这里可能会发生什么？我正在Microsoft Access中使用SQL查询。谢谢。

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';

原文

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';

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

谈情不如逗狗 2025-02-12 23:12:49

老实说，您的查询并没有很多意义。在您拥有tbl_payments.amount =（select sum（tbl_payments.amount）＆lt; = 0的情况下，该子查询只会概括表中每个记录的“数量”，无论哪个deviceID。另外，您正在寻找tbl_payments表中的一个记录，其中金额= tbl_payments中所有金额的总和？

我建议您的查询可能需要更像是这样：

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';

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';

回复收藏 0 原文

蓝梦月影 2025-02-12 23:12:49

当前，该子查询不将特定ID与外部查询相关联，并且您还指定了子程序的选择＆lt; = 0 select 子句。考虑在条件中使用logic在中调整in的子句，并使用表别名区分相同的表格。

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
)

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
)

回复收藏 0 原文

~没有更多了~