• CONTEST ALERT - Experience the power of DDR5 memory with Kingston Click for details

MySQL Query - Can anyone help?

Status
Not open for further replies.

DizitalNovice

I don't want IT
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
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)
The problem:
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)>3
returns multiple rows with same book_id. available copies increases only by 1...

I hope you understand the problm. can you help?
 

victor_rambo

हॉर्न ओके प्लीज़
Code:
UPDATE books SET available_copies=available_copies+(SELECT COUNT(*) FROM lib_request WHERE lib_request.book_id=books.book_id AND datediff(curdate(),l.date)>3)
This should work!
 
OP
DizitalNovice

DizitalNovice

I don't want IT
Let me try and tell you
I think it does work...
Thank you...
but can you explain this query...
 
Last edited:
Status
Not open for further replies.
Top Bottom