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