By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t1 (`order` CHAR(3), item CHAR(3), Qty INT);
INSERT INTO t1 VALUES
('ABC' , 'IA1' , 5),
('ABC' , 'IA2' , 6),
('ABC' , 'IA3' , 1),
('ABD' , 'IA4' , 2),
('ABD' , 'IA5' , 2),
('ABE' , 'IA6' , 1),
('ABE' , 'IA7' , 2);
CREATE TABLE t2 (`order` CHAR(3), Qty INT);
INSERT INTO t2 VALUES
('ABC' , 9),
('ABD' , 3),
('ABE' , 3);
SELECT t1.*,
CASE WHEN t2.Qty <SUM(t1.Qty) OVER (PARTITION BY `order` ORDER BY t1.item)
THEN GREATEST(t2.Qty + t1.Qty - SUM(t1.Qty) OVER (PARTITION BY `order` ORDER BY t1.item), 0)
ELSE t1.Qty
END ordered_Qty
FROM t1
JOIN t2 USING (`order`)
| order | item | Qty | ordered_Qty |
|---|---|---|---|
| ABC | IA1 | 5 | 5 |
| ABC | IA2 | 6 | 4 |
| ABC | IA3 | 1 | 0 |
| ABD | IA4 | 2 | 2 |
| ABD | IA5 | 2 | 1 |
| ABE | IA6 | 1 | 1 |
| ABE | IA7 | 2 | 2 |