Paste this into a new question or an answer at dba.stackexchange.com:
<!-- -->
> create table Library (BranchNo varchar(20), BookShelfNo varchar(20) primary key, BookId varchar(20), BookSupplerNo int)
>
> <pre>
> ✓
> </pre>
<!-- -->
> insert into Library values('1234','4545',666,'')
>
> <pre>
1 rows affected
> </pre>
<!-- -->
> insert into Library values('1234','4546',667,'')
>
> <pre>
1 rows affected
> </pre>
<!-- -->
> insert into Library values('1234','4547',668,'')
>
> <pre>
1 rows affected
> </pre>
<!-- -->
> insert into Library values('1234','4550',668,'')
>
> <pre>
1 rows affected
> </pre>
<!-- -->
> create table BookSupplier(BookNo varchar(20), SupplierNo int)
>
> <pre>
> ✓
> </pre>
<!-- -->
> insert into BookSupplier values('666',9112)
>
> <pre>
1 rows affected
> </pre>
<!-- -->
> insert into BookSupplier values('667',9897)
>
> <pre>
1 rows affected
> </pre>
<!-- -->
> insert into BookSupplier values('667',9998)
>
> <pre>
1 rows affected
> </pre>
<!-- -->
> insert into BookSupplier values('668',9545)
>
> <pre>
1 rows affected
> </pre>
<!-- -->
> select * from Library
>
> <pre>
> BRANCHNO | BOOKSHELFNO | BOOKID | BOOKSUPPLERNO
> :------- | :---------- | :----- | ------------:
> 1234 | 4545 | 666 | <em>null</em>
> 1234 | 4546 | 667 | <em>null</em>
> 1234 | 4547 | 668 | <em>null</em>
> 1234 | 4550 | 668 | <em>null</em>
> </pre>
<!-- -->
> select * from BookSupplier
>
> <pre>
> BOOKNO | SUPPLIERNO
> :----- | ---------:
> 666 | 9112
> 667 | 9897
> 667 | 9998
> 668 | 9545
> </pre>
<!-- -->
> update Library set BOOKSUPPLERNO =
> (select SupplierNo from BookSupplier where BookNo in (select BookId
> from Library where BookShelfNo in ('4545','4546','4550')))
>
> <pre>
> ORA-01427: single-row subquery returns more than one row
> </pre>
<!-- -->
> 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
>
> <pre>
> ORA-30926: unable to get a stable set of rows in the source tables
> </pre>
<!-- -->
> 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 )
>
> <pre>
4 rows affected
> </pre>
<!-- -->
> select * from library
>
> <pre>
> BRANCHNO | BOOKSHELFNO | BOOKID | BOOKSUPPLERNO
> :------- | :---------- | :----- | ------------:
> 1234 | 4545 | 666 | 9112
> 1234 | 4546 | 667 | 9998
> 1234 | 4547 | 668 | 9545
> 1234 | 4550 | 668 | 9545
> </pre>
<!-- -->
> update library set BOOKSUPPLERNO = null
>
> <pre>
4 rows affected
> </pre>
<!-- -->
> select * from library
>
> <pre>
> BRANCHNO | BOOKSHELFNO | BOOKID | BOOKSUPPLERNO
> :------- | :---------- | :----- | ------------:
> 1234 | 4545 | 666 | <em>null</em>
> 1234 | 4546 | 667 | <em>null</em>
> 1234 | 4547 | 668 | <em>null</em>
> 1234 | 4550 | 668 | <em>null</em>
> </pre>
<!-- -->
> 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 )
>
> <pre>
4 rows affected
> </pre>
<!-- -->
> select * from library
>
> <pre>
> BRANCHNO | BOOKSHELFNO | BOOKID | BOOKSUPPLERNO
> :------- | :---------- | :----- | ------------:
> 1234 | 4545 | 666 | 9112
> 1234 | 4546 | 667 | 9998
> 1234 | 4547 | 668 | <em>null</em>
> 1234 | 4550 | 668 | <em>null</em>
> </pre>
<!-- -->
> 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 )
>
> <pre>
4 rows affected
> </pre>
<!-- -->
> select * from library
>
> <pre>
> BRANCHNO | BOOKSHELFNO | BOOKID | BOOKSUPPLERNO
> :------- | :---------- | :----- | ------------:
> 1234 | 4545 | 666 | 9112
> 1234 | 4546 | 667 | 9998
> 1234 | 4547 | 668 | <em>null</em>
> 1234 | 4550 | 668 | <em>null</em>
> </pre>
<!-- -->
> 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')
>
> <pre>
> ORA-30926: unable to get a stable set of rows in the source tables
> </pre>
*db<>fiddle [here](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=4b95f9d47469e34981450d6c851a7f43)*
back to fiddle