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))

Example data would help undertanding your problem – juergen d Oct 26 '13 at 06:20 — juergen d, Oct 26 '13 at 06:20

score 0 · Accepted Answer · answered Oct 26 '13 at 06:28

0

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')

answered Oct 26 '13 at 06:28

Vijay Hulmani

969
8
17

score 0 · Answer 2 · answered Oct 26 '13 at 06:28

You need to use UPDATE command. First be sure that that are your records. For that select first:

select * from albums where shelfnumber in (
    select shelfNumber from albums where albumcode in (
    select albumcode from loans where borrowerid='RS96FM'))

if that are yours records, update:

update albums set shelfnumber=0 where shelfnumber in (
    select shelfNumber from albums where albumcode in (
    select albumcode from loans where borrowerid='RS96FM'))

Finding and updating a value

2 Answers2