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.
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'?.