Question: (MySQL Syntax) Write a single UPDATE statement to change the due date for all books borrowed on same day as the borrowing patron's sign-up. These due dates should be changed to exactly 30 days after patron sign-up. Here are the tables: CREATE TABLE book( isbn VARCHAR(100) PRIMARY KEY, title VARCHAR(100) NOT NULL, author VARCHAR(100) NOT NULL, pub_date DATE ); CREATE TABLE patron( id INT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, sign_up DATE NOT NULL ); CREATE TABLE borrow( patron INT NOT NULL, book VARCHAR(100) NOT NULL, check_out_date DATE NOT NULL, due_date DATE NOT NULL, PRIMARY KEY(patron, book, check_out_date), FOREIGN KEY (patron) REFERENCES patron(id), FOREIGN KEY (book) REFERENCES book(isbn) ); CREATE TABLE late_notice( patron INT NOT NULL, book VARCHAR(100) NOT NULL, check_out_date DATE NOT NULL, notice_date DATE, FOREIGN KEY (patron, book, check_out_date) REFERENCES borrow(patron, book, check_out_date) );
Question: (MySQL Syntax)
Write a single UPDATE statement to change the due date for all books borrowed on same day as the borrowing patron's sign-up. These due dates should be changed to exactly 30 days after patron sign-up.
Here are the tables:
CREATE TABLE book(
isbn VARCHAR(100) PRIMARY KEY,
title VARCHAR(100) NOT NULL,
author VARCHAR(100) NOT NULL,
pub_date DATE
);
CREATE TABLE patron(
id INT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
sign_up DATE NOT NULL
);
CREATE TABLE borrow(
patron INT NOT NULL,
book VARCHAR(100) NOT NULL,
check_out_date DATE NOT NULL,
due_date DATE NOT NULL,
PRIMARY KEY(patron, book, check_out_date),
FOREIGN KEY (patron) REFERENCES patron(id),
FOREIGN KEY (book) REFERENCES book(isbn)
);
CREATE TABLE late_notice(
patron INT NOT NULL,
book VARCHAR(100) NOT NULL,
check_out_date DATE NOT NULL,
notice_date DATE,
FOREIGN KEY (patron, book, check_out_date) REFERENCES borrow(patron, book, check_out_date)
);
![](/static/compass_v2/shared-icons/check-mark.png)
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
![Blurred answer](/static/compass_v2/solution-images/blurred-answer.jpg)
This update statement is failing a test case. Any ideas on how to fix this?
![Test Case
3 or more borrow rows on date of patron sign up
After update date check
Other records unaffected
Expected Actual
Yes
Yes
0
Yes
2
Yes
SQL Statement
(hidden)
SELECT count(*)
FROM borrow b JOIN patron p ON b.patron = p.id
WHERE check_out_date= sign_up AND due_date <> DATE_ADD (sign_up, INTERVAL 30 DAY)
SELECT CASE WHEN count (*) > 0 THEN 'Yes' ELSE 'No' END
FROM borrow b JOIN patron p ON b.patron = p.id
WHERE due date <> DATE_ADD (check_out_date, INTERVAL 30 DAY)](https://content.bartleby.com/qna-images/question/ca976fb8-8742-4cef-b582-959b2a0d7941/598cefad-df6e-4926-b08d-4754cfb33f67/silgrmc_thumbnail.png)
![Computer Networking: A Top-Down Approach (7th Edi…](https://www.bartleby.com/isbn_cover_images/9780133594140/9780133594140_smallCoverImage.gif)
![Computer Organization and Design MIPS Edition, Fi…](https://www.bartleby.com/isbn_cover_images/9780124077263/9780124077263_smallCoverImage.gif)
![Network+ Guide to Networks (MindTap Course List)](https://www.bartleby.com/isbn_cover_images/9781337569330/9781337569330_smallCoverImage.gif)
![Computer Networking: A Top-Down Approach (7th Edi…](https://www.bartleby.com/isbn_cover_images/9780133594140/9780133594140_smallCoverImage.gif)
![Computer Organization and Design MIPS Edition, Fi…](https://www.bartleby.com/isbn_cover_images/9780124077263/9780124077263_smallCoverImage.gif)
![Network+ Guide to Networks (MindTap Course List)](https://www.bartleby.com/isbn_cover_images/9781337569330/9781337569330_smallCoverImage.gif)
![Concepts of Database Management](https://www.bartleby.com/isbn_cover_images/9781337093422/9781337093422_smallCoverImage.gif)
![Prelude to Programming](https://www.bartleby.com/isbn_cover_images/9780133750423/9780133750423_smallCoverImage.jpg)
![Sc Business Data Communications and Networking, T…](https://www.bartleby.com/isbn_cover_images/9781119368830/9781119368830_smallCoverImage.gif)