0

I have a use case where I want to update multiple records in two different tables in one go. The records may be well over 3000 or 5000. I tried doing it with PL/SQL code. The code is working fine except for the fact that it updates only 1000 records. If the records are over 1000, it doesnt do anything. Below is my sql cursor :

declare
    cursor book_update IS
        Select book_id from books where bookNumber IN ('1','2','3','4','5','6','7','8','9' ..........  '1003');
begin
    for bk in book_update loop
        update author set active='true' where book_id=bk.book_id;
        update books set is_available=1 where book_id = bk.book_id
    end loop;
end;

/

Is there any way by which I can update any number of records put to a sql cursor ?

Thanks

MT0
  • 143,790
  • 11
  • 59
  • 117
aryan rai
  • 41
  • 1
  • 4
  • 1
    Why do you even use a cursor instead of directly updating with appropriate `WHERE` clauses. Using cursors can be painfully slow compared to set based operations. – sticky bit Nov 17 '21 at 12:22
  • The records may be huge and dont want to add multiple set statements with all the ids again and again. Thats my main concern otherwise I can do it via normal statements also. – aryan rai Nov 17 '21 at 12:25
  • Is this your complete code ? I'd expect ```ORA-01795: maximum number of expressions in a list is 1000``` so I'm assuming you're doing a ```WHEN OTHERS THEN NULL``` which explains why it seems to be "doing nothing" – Koen Lostrie Nov 17 '21 at 12:27
  • You can use a collection [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=dc1bc13bf96b64eccb6cb5dba5afc2d7) – MT0 Nov 17 '21 at 12:46
  • @aryanrai @MT0 Then you do not need any cursors and may avoid reading `book` table twice: use `forall...` and `bulk collect` to update `books` and then `forall ...` to update `author` by updated `book_id`s. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=44eafa642d3582f14a09145085f9bf1a) – astentx Nov 17 '21 at 13:36
  • @astentx That is true but the OP's question was about the 1000 limit on the `IN` clause. It may be that they have an XY-problem and should be using `FORALL` but I was answering their question. (You can also do it without `FORALL` [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=77b05fcbf3df0342c2bb2ee58c850067).) – MT0 Nov 17 '21 at 13:41

0 Answers0