Finding and updating a value

Question

I have two tables; Albums and Loans. I have a BorrowerId = RS96FM in the table loans that has lost its content and therefore I want to update the ShelfNumber in the the albums to 0. However I do not know how to go about this as I first have to find the AlbumCode of what the BorrowerId has and then change the ShelfNumber for whatever AlbumCode they have out in Albums.

Below is what my sql statement currently looks like so far.

Select  AlbumCode, Performer, AlbumTitle, ShelfNumber, YearReleased, RecordCompanyCode
from    Albums 
where   ShelfNumber =(Select ShelfNumber
                    from    Albums
                    where   AlbumCode =  (Select AlbumCode
                                        from    Loans
                                        where Borrowerid ='RS96FM'
                                        **and update(Shelfnumber) = 0))

Answer 1

i guess the sub query will work. The below query format is for sql server.

Update Albums
set ShelfNumber=0
where   AlbumCode in  (Select AlbumCode
                     from    Loans
                     where Borrowerid ='RS96FM')