Home » SQL & PL/SQL » SQL & PL/SQL » How to update multiple rows with different values from another table in the same query? (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
How to update multiple rows with different values from another table in the same query? [message #686145] Thu, 23 June 2022 05:49 Go to next message
a_naq
Messages: 13
Registered: April 2021
Junior Member
I have a table Library

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

insert into Library values('1234','4545',666,'')
insert into Library values('1234','4546',667,'')
insert into Library values('1234','4547',668,'')
insert into Library values('1234','4550',668,'')
Another Table BookSupplier

create table BookSupplier(BookNo varchar(20), SupplierNo int)

insert into BookSupplier values('666',9112)
insert into BookSupplier values('667',9897)
insert into BookSupplier values('667',9998)
insert into BookSupplier values('668',9545)



select * from Library


BranchNo BookShelfNo BookId BookSupplerNo

1234      4545        666     

1234      4546        667

1234      4547        668  

1234      4550        668



select * from 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 gives me error ORA-01427: single-row subquery returns more than one row


Then I tried 


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
It gave me error ORA-30926: unable to get a stable set of rows in the source tables.

Plus, someone told me that merge is not a good approach as it sometimes update whats not meant to be updated. So, please help me. Thank you!

Update : I tried this also

UPDATE Library lib
   SET BOOKSUPPLERNO = (SELECT SupplierNo
                         FROM BookSupplier bs
                        WHERE lib.BookId = bs.BookNo)
 WHERE EXISTS (
    SELECT 1
      FROM BookSupplier bs
     WHERE lib.BookId = bs.BookNo )
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.

[Updated on: Thu, 23 June 2022 07:24]

Report message to a moderator

Re: How to update multiple rows with different values from another table in the same query? [message #686147 is a reply to message #686145] Thu, 23 June 2022 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
Plus, someone told me that merge is not a good approach as it sometimes update whats not meant to be updated.
MERGE does what you tell you to do. If you don't get the expected result then you didn't correctly write the statement.


Quote:
It gives me error ORA-01427: single-row subquery returns more than one row
It is expected.
You have 2 rows in BookSupplier for book 667 so how Oracle would know which one you want it to return? Do you know which one has to be returned?

Your "working" statement can't work for the same reason:
TEST> UPDATE Library lib
  2     SET BOOKSUPPLERNO = (SELECT SupplierNo
  3                           FROM BookSupplier bs
  4                          WHERE lib.BookId = bs.BookNo)
  5   WHERE EXISTS (
  6      SELECT 1
  7        FROM BookSupplier bs
  8       WHERE lib.BookId = bs.BookNo )
  9  /
   SET BOOKSUPPLERNO = (SELECT SupplierNo
                        *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

Report message to a moderator

Re: How to update multiple rows with different values from another table in the same query? [message #686149 is a reply to message #686147] Thu, 23 June 2022 08:42 Go to previous messageGo to next message
a_naq
Messages: 13
Registered: April 2021
Junior Member
Quote:
You have 2 rows in BookSupplier for book 667 so how Oracle would know which one you want it to return? Do you know which one has to be returned?
yes, we can use either of the two values.


Quote:
Your "working" statement can't work for the same reason:
sorry I forgot to post the updated query 

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 )
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=4b95f9d47469e34981450d6c851a7f43

But this query isn't very helpful because when I ran it against my real data which contains thousands of records..it just froze...
I think the reason is that in correlated sub queries, it scans the entire rows of the table and since my production table has thousands of data so its just getting timed out.

Please help me how can I run this query in less time.

[Updated on: Thu, 23 June 2022 09:10]

Report message to a moderator

Re: How to update multiple rows with different values from another table in the same query? [message #686150 is a reply to message #686149] Thu, 23 June 2022 09:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
yes, we can use either of the two values.

In this case, just:
TEST> update Library l
  2  set BOOKSUPPLERNO =
  3      (select SupplierNo from BookSupplier where BookNo = l.BookId and rownum=1)
  4  where BookShelfNo in ('4545','4546','4550')
  5  /

3 rows updated.

TEST> select * from Library;
BRANCHNO             BOOKSHELFNO          BOOKID               BOOKSUPPLERNO
-------------------- -------------------- -------------------- -------------
1234                 4545                 666                           9112
1234                 4546                 667                           9897
1234                 4547                 668
1234                 4550                 668                           9545

4 rows selected.

Report message to a moderator

Re: How to update multiple rows with different values from another table in the same query? [message #686151 is a reply to message #686150] Thu, 23 June 2022 10:46 Go to previous message
a_naq
Messages: 13
Registered: April 2021
Junior Member
Thanks a lot! It worked Smile

Report message to a moderator

Previous Topic: Display average as part of result
Next Topic: BASIC SYNTAX / Dynamic Pivot Table (merged)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Apr 28 21:46:22 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.