Update parent table column only if all the values of child table rows are a specific value

I have 2 tables, orders and order_items. Each order can have multiple order_items.

An order has an id and orderStatus:

An order_item as id, orderId and orderItemStatus:

I want to update the status of order’s from OPEN to FULFILLED if all an order’s order_items have status of ISSUED. If at least one order_item is not ISSUED, the order status should remain as OPEN. How can I do this?

Answer

You can use a NOT EXISTS subquery as condition.

update order o
set o.orderStatus = 'FULFILLED'
where o.orderStatus = 'OPEN'
  and not exists (
    select *
    from order_item i
    where i.orderId = o.id
      and i.orderItemStatus <> 'ISSUED'
  )

This will only update orders which have status ‘OPEN’ and have no items with status ‘ISSUED’.