UPDATE parents
    SET large_family = CASE
                       WHEN (SELECT COUNT(*) FROM children WHERE parent_id = parents.id) > 2 THEN 1
                       ELSE 0
                       END

这是我的经验：对于大多数update查询，我编写它的select查询，然后将其更改为update。这样做非常简单。

update p set large_family="true"
from
parents p
inner join chidren c
on(p.id = c.parent_id)
group by p.parent_id
having count(c.id)>2

加入表格 parents 返回所有 parent_id 有两个以上孩子的：

update parents p
inner join (
  select parent_id
  from children
  group by parent_id
  having count(*) > 2
) t on t.parent_id = p.id
set p.large_family = true

这种语法适用于mysql。

UPDATE parents 
SET    large_family = (SELECT CASE 
                                WHEN childcnt > 1 THEN 1 
                                ELSE 0 
                              END 
                       FROM   (SELECT parent_id, 
                                      Count(*) CHILDCNT 
                               FROM   children 
                               WHERE  parents.id = children.parent_id 
                               GROUP  BY parent_id)) 
WHERE  EXISTS (SELECT 1 
               FROM   children 
               WHERE  parents.id = children.parent_id);