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 from Oracle SQL: Update a table with data from another table

MERGE INTO Library lib
USING
(
SELECT 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')

It says,

ORA-30926: unable to get a stable set of rows in the source tables

I know it is because I have duplicates in BookSupplier column, but then I added distinct like this

MERGE INTO Library lib
USING
(
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')

Now , it says

ORA-30926: unable to get a stable set of rows in the source tables

CodePudding user response：

The diagnosis is correct, but the cure you tried makes no sense. The pairs (bookno, supplierno) are already distinct - what did you expect adding DISTINCT to do there?

Rather, the subquery should somehow get only one row (one single supplierno) for each bookno. For example:

......
using
(select bookno, max(supplierno) as supplierno from booksupplier group by bookno) bs
......