Update Quantity Available using Quantity from another table

Question

1. You will demonstrate an update that includes two tables and uses a subquery.

   Make sure to prove that your update executed correctly by showing data in the tables before and after the update.

I'd like to update the quantity available based on quantity ordered - table book Available = Available - quantity from orders table

This is what I have so far: BUT IT's not Working

SELECT Available 
FROM   book 
WHERE  Available IN (SELECT ALL(Available) 
                     FROM   orders 
                     WHERE  Quantity >= 1);

UPDATE book 
SET book.Available = Available - (SELECT orders.quantity 
                                  FROM orders 
                                  WHERE orders.ISBN_No=book.ISBN_No) 
WHERE Available IN (SELECT ALL(Available) 
                    FROM orders 
                    WHERE Quantity >= 1);

SELECT Available
FROM book
WHERE Available IN (SELECT ALL(Available) 
                    FROM orders 
                    WHERE Quantity >= 1);

Here are the 2 tables:

CREATE TABLE book
(
ISBN_No INT(10) PRIMARY KEY,
Title VARCHAR(30),
Author VARCHAR(50),
Publisher VARCHAR(50),
Publication_Date DATE,
Genre VARCHAR(25),
Condition_type VARCHAR(5),
Cost DECIMAL(8,2),
Available INT(5)
);

INSERT INTO book VALUES
(0234567890, 'Harry Potter Collection', 'J.K. Rowling', 'Bloomsbury', '2010-01-01', 'Children', 'NEW', '65.00', 7),
(0987654321, 'Lord of the Rings Collection', 'J.R.R. Tolkein', 'Harper', '2001-02-15', 'Fantasy', 'USEDA', '18.00', 1),
(0421548752, 'The Help', 'Kathryn Stockett', 'Del Rey', '2015-03-25', 'Fiction', 'USEDG', '9.00', 3),
(0545454235, 'Pride and Prejudice', 'Jane Austin', 'Scholastic', '1999-12-05', 'Classic', 'USEDG', '12.00', 2),
(0458742154, 'Alexander Hamilton', 'Ron Chernow', 'Scholastic', '2017-06-12', 'Biography', 'NEW', '24.00', 12);

CREATE TABLE orders
(
Order_No INT(6) PRIMARY KEY,
Order_date DATE,
ISBN_No INT(10) NOT NULL,
Quantity INT(5),
Cost DECIMAL(8,2),
Total DECIMAL(8,2),
Delivery_Method VARCHAR(6),
Payment_type VARCHAR(12) NOT NULL,
Credit_Card VARCHAR(16),
Credit_Exp DATE,
Employee_ID INT(3),
Customer_No INT(11) NOT NULL,  

FOREIGN KEY (Customer_No)
REFERENCES customer(Customer_No),
FOREIGN KEY (Employee_ID)
REFERENCES employee(Employee_ID),
FOREIGN KEY (ISBN_No) 
REFERENCES book(ISBN_No)
);

INSERT INTO orders VALUES 
(101112, '2018/01/29', 0234567890, '2', 65.00, 130.00, 'UPS', 'D', '54661625258778596', '2020/01/22', 004, 100100),
(101100, '2018/01/18', 0421548752, '1', 9.00, 9.00, 'UPS', 'CC', '8546587785965222', '2020/12/22', 004, 100100),
(101106, '2018/01/23', 0545454235, '1', 12.00, 12.00, 'USPS', 'CASH', 'NULL', 'NULL', 004, 784521),
(101109, '2018/01/25', 0458742154, '5', 24.00, 120.00, 'USPS', 'CC', '6541254265874856', '2025/02/22', 005, 784521),
(101105, '2018/01/27', 0545454235, '1', 12.00, 12.00, 'USPS E', 'CASH', 'NULL', 'NULL', 004, 784521);

I'm stuck. What I have above I've tried changing in so many different ways to no avail.