create table Library (BranchNo varchar(20), BookShelfNo varchar(20) primary key, BookId varchar(20), BookSupplerNo int)

insert into Library values('1234','4545',666,'')

insert into Library values('1234','4546',667,'')

insert into Library values('1234','4547',668,'')

insert into Library values('1234','4550',668,'')

create table BookSupplier(BookNo varchar(20), SupplierNo int)

insert into BookSupplier values('666',9112)

insert into BookSupplier values('667',9897)

insert into BookSupplier values('667',9998)

insert into BookSupplier values('668',9545)

select * from Library

select * from BookSupplier

update Library set BOOKSUPPLERNO = (select SupplierNo from BookSupplier where BookNo in (select BookId from Library where BookShelfNo in ('4545','4546','4550')))

merge into Library lib using BookSupplier bs on ( lib.BookId = bs.BookNo and lib.BookShelfNo in ('4545','4546','4550')) when matched then update set lib.BOOKSUPPLERNO = bs.SupplierNo

UPDATE Library lib SET BOOKSUPPLERNO = (SELECT max(SupplierNo) FROM BookSupplier bs WHERE lib.BookId = bs.BookNo) WHERE EXISTS ( SELECT 1 FROM BookSupplier bs WHERE lib.BookId = bs.BookNo )

select * from library

update library set BOOKSUPPLERNO = null

select * from library

UPDATE Library lib SET BOOKSUPPLERNO = (SELECT max(SupplierNo) FROM BookSupplier bs WHERE lib.BookId = bs.BookNo and lib.BookShelfNo in ('4545','4546')) WHERE EXISTS ( SELECT 1 FROM BookSupplier bs WHERE lib.BookId = bs.BookNo )

select * from library

UPDATE Library lib SET lib.BOOKSUPPLERNO = (SELECT max(bs.SupplierNo) FROM BookSupplier bs WHERE lib.BookId = bs.BookNo and lib.BookShelfNo in ('4545','4546')) WHERE EXISTS ( SELECT 1 FROM BookSupplier bs WHERE lib.BookId = bs.BookNo )

select * from library

MERGE INTO Library lib USING ( -- For more complicated queries you can use WITH clause here SELECT distinct BookNo, SupplierNo FROM BookSupplier )bs ON(lib.BookId = bs.BookNo) WHEN MATCHED THEN UPDATE SET lib.BOOKSUPPLERNO = bs.SupplierNo where lib.BookShelfNo in ('4545','4546')