用于更新字段的sql查询

z31licg0  于 2021-08-09  发布在  Java
关注(0)|答案(4)|浏览(314)

我们有两张table:

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

如果父级有两个以上的子级,我需要一个sql查询来将parents表中的“large\u family”字段更新为“true”。

czq61nw1

czq61nw11#

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

w7t8yxp52#

这是我的经验:对于大多数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
pkmbmrz7

pkmbmrz73#

加入表格 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。

6ojccjat

6ojccjat4#

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

相关问题