I have two tables : a) BookFine and b) IssuedBook.
In BookFine, I have the columns RegNo and LateFee where I am storing just the Late Fees of delayed Books against the Registration Number of Student.
In IssuedBook I have the columns RegNo, BookTitle, IssueDate and ReturnDate.
I want to achieve this thing involving both tables :
Whenever a student delays a book after the ReturnDate ( Like if ReturnDate < Current Date ) then the LateFee Column in BookFine table should be incremented by 1 where BookFine.RegNo = IssuedBook.RegNo.
I have successfully done this through SQL Job Schedules but I wonder if this is the way it should be done or is there any other good way of doing it. Note that I am using SQL Server 2008 r2.
Thanks.
The syntax you're looking for is:
UPDATE BookFine
SET LateFee = LateFee +1
WHERE RegNo IN (SELECT RegNo FROM IssuedBook WHERE ReturnDate < CurrentDate)
But you need to handle cases like if there are no records for this student in BookFine, this could be done by first running a prep statement to initialize a zero record. (Putting both of these in a transaction would prevent stray 0 records from being made)
INSERT INTO BookFine (RegNo, LateFee)
SELECT RegNo, 0
FROM IssuedBook
WHERE ReturnDate < CurrentDate
AND RegNo NOT IN (SELECT RegNo FROM BookFine)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments