Query to update column in one table based on conditions met in another table
I think this solution will work.

--demo setup
DROP TABLE IF EXISTS tblOrders;
DROP TABLE IF EXISTS tblUnits;
CREATE TABLE tblOrders (
  Order_ID INTEGER,
  Status INTEGER
);

INSERT INTO tblOrders
  (Order_ID, Status)
VALUES
  ('100', '0'),
  ('101', '0'),
  ('102', '0');

  CREATE TABLE tblUnits (
  Unit_ID INTEGER,
  Status INTEGER,
  Order_Id INTEGER
);

INSERT INTO tblUnits
  (Unit_ID, Status, Order_Id)
VALUES
  ('1', '1', '100'),
  ('2', '1', '100'),
  ('3', '1', '100'),
  ('4', '1', '101'),
  ('5', '1', '101'),
  ('6', '0', '101'),
  ('7', '0', '101');


--solution
UPDATE o
SET o.Status = 1
FROM tblOrders o
--check to see if there are units for the order
WHERE EXISTS (  
        SELECT *
        FROM tblUnits u
        WHERE u.Order_Id = o.Order_ID
        )
--make sure there are NO units that are not status = 1
    AND NOT EXISTS (
        SELECT *
        FROM tblUnits u
        WHERE u.Order_Id = o.Order_ID
            AND u.STATUS &lt;&gt; 1
        )
--verify update
select * from tblOrders




| Order_ID | Status |
|----------|--------|
| 100      | 1      |
| 101      | 0      |
| 102      | 0      |