This seems so simple yet I just cannot get it!
The context:
I am building a library mgmt system and there is a table called requests where current requests for book id are kept as (buk_id,usr_id,Date of rqst).
Now everytime an entry is made here number of available copies in the books table decreases by 1. The requests are valid for 2 days after which entry is deleted from requests table and available copies is increased by 1.
I used query
The problem:
Whenever the same book exists more than once in the invalid requests query ie.
returns multiple rows with same book_id. available copies increases only by 1...
I hope you understand the problm. can you help?
The context:
I am building a library mgmt system and there is a table called requests where current requests for book id are kept as (buk_id,usr_id,Date of rqst).
Now everytime an entry is made here number of available copies in the books table decreases by 1. The requests are valid for 2 days after which entry is deleted from requests table and available copies is increased by 1.
I used query
Code:
update books b set available_copies=available_copies+1 where b.book_id in( SELECT book_id FROM `lib_request` l WHERE datediff(curdate(),l.date)>3)Whenever the same book exists more than once in the invalid requests query ie.
Code:
SELECT book_id FROM `lib_request` l WHERE datediff(curdate(),l.date)>3I hope you understand the problm. can you help?
