By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table [order] (Order_Id int, Customer varchar(20), Status int);
insert into [order](Order_Id, Customer, Status) values
(101, 'Abc', 1),
(102, 'xyz', 1);
2 rows affected
create table suborder (Sub_Order_Id int, Order_Id int, Sub_order_status int);
insert into suborder (Sub_Order_Id, Order_Id, Sub_order_status) values
(10101, 101, 2),
(10102, 101, 2),
(10103, 101, 2),
(10201, 102, 1);
4 rows affected
update o
set status = 2
from [order] o
where not exists (
select 1 from suborder
where order_id = o.order_id and sub_order_status <> 2
)
1 rows affected
select * from [order]
| Order_Id | Customer | Status |
|---|---|---|
| 101 | Abc | 2 |
| 102 | xyz | 1 |