clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798962 fiddles created (41846 in the last week).

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


insert into Library values('1234','4545',666,'')
1 rows affected
 hidden batch(es)


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


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


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


create table BookSupplier(BookNo varchar(20), SupplierNo int)
 hidden batch(es)


insert into BookSupplier values('666',9112)
1 rows affected
 hidden batch(es)


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


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


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


select * from Library
BRANCHNO BOOKSHELFNO BOOKID BOOKSUPPLERNO
1234 4545 666
1234 4546 667
1234 4547 668
1234 4550 668
 hidden batch(es)


select * from BookSupplier
BOOKNO SUPPLIERNO
666 9112
667 9897
667 9998
668 9545
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


select * from library
BRANCHNO BOOKSHELFNO BOOKID BOOKSUPPLERNO
1234 4545 666 9112
1234 4546 667 9998
1234 4547 668 9545
1234 4550 668 9545
 hidden batch(es)


update library set BOOKSUPPLERNO = null
4 rows affected
 hidden batch(es)


select * from library
BRANCHNO BOOKSHELFNO BOOKID BOOKSUPPLERNO
1234 4545 666
1234 4546 667
1234 4547 668
1234 4550 668
 hidden batch(es)


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
 hidden batch(es)


select * from library
BRANCHNO BOOKSHELFNO BOOKID BOOKSUPPLERNO
1234 4545 666 9112
1234 4546 667 9998
1234 4547 668
1234 4550 668
 hidden batch(es)


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
 hidden batch(es)


select * from library
BRANCHNO BOOKSHELFNO BOOKID BOOKSUPPLERNO
1234 4545 666 9112
1234 4546 667 9998
1234 4547 668
1234 4550 668
 hidden batch(es)


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
 hidden batch(es)