Update parent record when all childs are processed

问题: I have a master-detail relationship between two tables, I need to update one field of the master table when all records of the detail table are processed. For example:...

问题:

I have a master-detail relationship between two tables, I need to update one field of the master table when all records of the detail table are processed.

For example:

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

Then records of table detail are filled by a long-lasting process (i.e. a person changes the status of a record when a box is filled with products, then after some time it changes the status of another record and so on after filling all the boxes related to a truck) and I need to know the global status of the process, just by querying the master table.

How can I create an update query on the master table to change the status when ALL the elements of the detail table contain the value 'FILLED'?.


回答1:

Assuming that the current idmaster is for example 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;
  • 发表于 2019-01-18 19:56
  • 阅读 ( 174 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除