只有当所有匹配的行都会更新时才可以更新mysql表吗？

I have a table that looks like this

我有一张看起来像这样的桌子

| id | location | status | parent_id |   
-----------------------------------  
| 1  |  35      |   0    |   100     |  
| 2  |  35      |   1    |   100     |  
| 3  |  36      |   0    |   200     |  
| 4  |  36      |   0    |   200     |

I want to be able to update the group and set status = 0 of rows that match a certain parent_id if and only if all of the rows with the particular parent_id have status set to 0.

我希望能够更新组并设置与某个parent_id匹配的行的status = 0当且仅当具有特定parent_id的所有行的状态都设置为0时。

The use case is something like this: An activity (parent_id) may be done at one of multiple locations. Each activity has some sub-activities. An activity may move from one location to another only if none of the sub-activities has started.

用例是这样的：活动（parent_id）可以在多个位置之一完成。每项活动都有一些子活动。只有在没有子活动开始的情况下，活动才可以从一个位置移动到另一个位置。

Would it be possible to do it transactionally in MySQL? If it is possible to do it without locking the whole table it would be much more efficient, but I am not sure if there is a single statement solution to that.

可以在MySQL中以事务方式进行吗？如果可以在不锁定整个表的情况下完成它，那将会更有效率，但我不确定是否有单一的语句解决方案。

Thanks in advance.

提前致谢。

EDIT : I missed out one important bit - the parent_id is the id to the table where the main activity is stored. Hence, a join of this table with itself on (id, parent_id) would be incorrect.

编辑：我错过了一个重要的位 - parent_id是存储主要活动的表的id。因此，此表与其自身（id，parent_id）的连接将是不正确的。

2 个解决方案

#1

How about this. I haven't tested and the performance may not be the best.

这个怎么样。我没有测试过，性能可能不是最好的。

UPDATE t 
JOIN (SELECT parent_id, max(status) AS ma, min(status) AS mi FROM t2 GROUP BY parent_id HAVING ma<=0 AND mi>=-1) AS children 
  ON t.id=children.parent_id 
SET status = 0;

EDIT
Changed to use max an min instead of sum to support the fact that status can be less than 0 and now also allowing -1 in the children to be updated.

编辑更改为使用max an min而不是sum来支持status可以小于0的事实，现在还允许更新子项中的-1。

#2

This is what I tried and it seems to be working:

这是我尝试过的，它似乎正在起作用：

UPDATE foo
    JOIN 
        (SELECT parent_id, COUNT(*) AS status_count FROM foo WHERE parent_id =   200 AND status IN (0, -1) 
         HAVING status_count = (SELECT COUNT(*) AS total_count FROM foo WHERE parent_id = 200)) 
    AS x ON x.parent_id = foo.parent_id
SET status = 0, group_id = 98;

#1