Oracle - How to update multiple rows with different values in the same query?-CodePudding

I have a table Library

BranchNo BookShelfNo BookId BookSupplerNo

1234      4545        666     

1234      4546        667

1234      4547        668  

1234      4550        668

Table : BookSupplier

BookNo  SupplierNo      

666       9112
667       9897
667       9998
668       9545

I need to write an update statement where first I have to supply only the list of bookshelf, and the update query should find out

The BookId for that BookShelfNo in the Library Table.
Find SupplierNo in for that Book in the BookSupplier Table.
Update BookSupplierNo in the Library table.

Note : BookShelfNo is unique. A BookId can have multiple SupplierNo and we can use any number

I had written something like this

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

It is giving me ORA-01427: single-row subquery returns more than one row

I tried this also

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.BookSupplierNo = bs.SupplierNo

But I am getting unable to get stable set of values in the source table (ORA - 30926)

UPDATE

I tried this and it seems to be working for this small set of data, but it doesn't work in my real world environment where I have thousands of data as it keeps on scanning the entire table and eventually it times out.


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 )

CodePudding user response：

you need get single row from BookSupplier for each bookNo in order to join with the Library table. As you mentioned that you can use any supplier, we can try using max/min aggregate funtion.

merge into Library lib 
using ( select bookno,max(SupplierNo) as SupplierNo from BookSupplier
group by bookno ) bs
on
( lib.BookId = bs.BookNo
  and lib.BookShelfNo in ('4545','4546','4550'))
when matched then
update set lib.BookSupplierNo = bs.SupplierNo

above will give you for each bookno , max SupplierNo and then you can join it with Library table on bookid and update the records accordingly.