Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Library (BranchNo varchar(20), BookShelfNo varchar(20) primary key, BookId varchar(20), BookSupplerNo int)
insert into Library values('1234','4545',666,'')
1 rows affected

insert into Library values('1234','4546',667,'')
1 rows affected

insert into Library values('1234','4547',668,'')
1 rows affected

insert into Library values('1234','4550',668,'')
1 rows affected

create table BookSupplier(BookNo varchar(20), SupplierNo int)
insert into BookSupplier values('666',9112)
1 rows affected

insert into BookSupplier values('667',9897)
1 rows affected

insert into BookSupplier values('667',9998) 
1 rows affected

insert into BookSupplier values('668',9545)
1 rows affected

select * from Library


BRANCHNO
BOOKSHELFNO
BOOKID
BOOKSUPPLERNO


1234
4545
666
null

1234
4546
667
null

1234
4547
668
null

1234
4550
668
null


select * from BookSupplier


BOOKNO
SUPPLIERNO


666
9112

667
9897

667
9998

668
9545


update Library set BOOKSUPPLERNO = 
(select SupplierNo from BookSupplier where BookNo in (select BookId 
from Library where BookShelfNo in ('4545','4546','4550')))
ORA-01427: single-row subquery returns more than one row

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
ORA-30926: unable to get a stable set of rows in the source tables

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 )
4 rows affected

select * from library


BRANCHNO
BOOKSHELFNO
BOOKID
BOOKSUPPLERNO


1234
4545
666
9112

1234
4546
667
9998

1234
4547
668
9545

1234
4550
668
9545


update library set BOOKSUPPLERNO = null
4 rows affected

select * from library


BRANCHNO
BOOKSHELFNO
BOOKID
BOOKSUPPLERNO


1234
4545
666
null

1234
4546
667
null

1234
4547
668
null

1234
4550
668
null


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 )
4 rows affected

select * from library


BRANCHNO
BOOKSHELFNO
BOOKID
BOOKSUPPLERNO


1234
4545
666
9112

1234
4546
667
9998

1234
4547
668
null

1234
4550
668
null


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 )
4 rows affected

select * from library


BRANCHNO
BOOKSHELFNO
BOOKID
BOOKSUPPLERNO


1234
4545
666
9112

1234
4546
667
9998

1234
4547
668
null

1234
4550
668
null


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')
ORA-30926: unable to get a stable set of rows in the source tables
BRANCHNO	BOOKSHELFNO	BOOKID	BOOKSUPPLERNO
1234	4545	666	null
1234	4546	667	null
1234	4547	668	null
1234	4550	668	null
BRANCHNO	BOOKSHELFNO	BOOKID	BOOKSUPPLERNO
1234	4545	666	9112
1234	4546	667	9998
1234	4547	668	9545
1234	4550	668	9545