Question
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
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)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started