sql - 处理所有子项时更新父项记录

Question

我的两个表之间存在主从关系，我需要在处理完明细表的所有记录后更新主表的一个字段。

例如:

create table master(idmaster serial not null, 
status varchar(10) default 'CREATED', 
primary key(idmaster));

create table detail(iddetail serial not null, 
idmaster integer references master(idmaster),
status varchar(10) default 'REGISTERED',
primary key(iddetail));

然后表 detail 的记录由一个持久的过程填充(即，当一个盒子装满产品时，一个人改变了记录的状态，然后在一段时间后它改变了记录的状态装满与卡车相关的所有箱子后的另一条记录等)，我需要通过查询主表来了解流程的全局状态。

当明细表的所有元素都包含值“FILLED”时，如何在主表上创建一个更新查询来更改状态？

Answer 1

假设当前的 idmaster 是例如 123:

update master
set status = case
    when exists(
        select 1
        from detail
        where idmaster = 123
        and status <> 'FILLED')
    then 'IN PROCESS'
    else 'COMPLETED'
    end
where idmaster = 123;