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)