用于更新字段的 SQL 查询

Question

我们有两个表：

parents ( id:int , name:string , large_family:boolean default false)
children (id:int , name:string , parent_id: foreign key to parents)

如果父级有两个以上的子级，我需要 SQL 查询将父表中的"large_family"字段更新为"true"。

Answer 1

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

Answer 2

这是我的经验：对于大多数更新查询，我编写其选择查询，然后将其更改为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

Answer 3

将表parents连接到返回具有 2 个以上子项的所有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。

Answer 4

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