同时更新和检查 [英] Update and checking at the same time

问题描述

我有如下表格



CatID | Total_Amount | Amount_paying | IsCompleted

----- | -------------- | --------------- | - ----------

CA01 | 2000年| 1700 | 0



在此表中，我必须检查Total_Amount和amount_paying是否相同？

其中total_amount是固定的，amount_paying将获得最大化每个插入语句后。



所以如果我插入一个记录

CatID | Total_Amount | Amount_paying | IsCompleted

----- | -------------- | ------------ | ----- -------

CA01 | 2000年| 1700 | 0

CA01 | 2000年| 300 | 1

这里我支付300，所以Total_Amount = Amount_Paying和IsCompleted = 1

I have table as follows

CatID| Total_Amount | Amount_paying |IsCompleted
-----|--------------|---------------|------------
CA01 | 2000 | 1700 | 0

In this table I have to check if Total_Amount and amount_paying is same or not?
Where total_amount is fixed and amount_paying will get maximize after each insert statement.

So If I insert a record
CatID| Total_Amount | Amount_paying |IsCompleted
-----|-------------- |------------ |------------
CA01 | 2000 | 1700 | 0
CA01 | 2000 | 300 | 1
Here I am paying 300 , so Total_Amount = Amount_Paying and IsCompleted=1

UPDATE tbl
    		SET is_completed = CASE WHEN SUM(amount_paying) = Total_Amount THEN 1 ELSE 0 END
            WHERE tbl.branch_id =@branchid AND CatID=@catid

给出`聚合可能不会出现在UPDATE语句的设置列表中。



我是什么尝试过：

giving `An aggregate may not appear in the set list of an UPDATE statement.`

What I have tried:

UPDATE tbl
SET is_completed = CASE WHEN SUM(amount_paying) = Total_Amount THEN 1 ELSE 0 END
WHERE tbl.branch_id =@branchid AND CatID=@catid

推荐答案

只需查看一下您的数据库设计就会发现一些基本问题：

1。 Total_Amount字段具有重复值，例如2000.如果这个字段仅依赖于CatID，那么它们不应该在一个单独的表中，比如`tbl2`？

2.你怎么知道哪个`Is_Completed`行插入`1`当`Total_Amount` ='Amount_Paying`时？也许它应该被移动到那个单独的表`tbl2`。

3.然后两个表`tbl`和`tbl2`可以链接到CatID字段。

请查看数据库设计简介 [ ^ ]

Just one look at your table reveals some fundamental problems with your database design:
1. The `Total_Amount` field has duplicate values, e.g. 2000. If this field is dependent on CatID only, shouldn't they be in a separate table, say `tbl2`?
2. How do you know which `Is_Completed` row to insert `1` when `Total_Amount` = `Amount_Paying`? May be it should be moved to that separate table `tbl2` too.
3. The two tables `tbl` and `tbl2` can then link through CatID field.
Check this out Introduction to database design[^]

这篇关于同时更新和检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！