This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Dec 5, 2012 10:23 PM by 976640 RSS

Update multiple rows using WHERE CURRENT OF?

976640 Newbie
Currently Being Moderated
Hello,

Here is table (Here i need lock on the selected row as this is common table and accessed by multiple threads.) -

MyTable (Uidpk nummber, name varchar2(255), value numbner);

The rows in the tables are-

1, Test1, 10
2, Test2, 20
3, Test3, 30
4, Test4, 40

Please note: In this table column value of "Name" is unique. That is Test1 can have only one row.

And following function created -

create or replace
Function UpdateMyRow
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
is
SELECT value
from MyTable
where name = name_in
FOR UPDATE;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 0;

else
update MyTable
SET vale = cnumber+2
WHERE CURRENT OF c1;

COMMIT;

end if;

close c1;

RETURN cnumber;

END;

The problem is: By using above function i can update only one row.
How to create scalable function which can update one or more rows in one go?
Here i need lock on the selected row as this is common table and accessed by multiple threads.

Thanks.
  • 1. Re: Update multiple rows using WHERE CURRENT OF?
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    Please read SQL and PL/SQL FAQ
    Post create table and insert statement next time.

    Additionally when you put some code please enclose it between two lines starting with {noformat}
    {noformat}
    i.e.:
    {noformat}
    {noformat}
    SELECT ...
    {noformat}
    {noformat}
    
    You can achieve this simply with SQL in this way:
    SELECT value
    INTO cnumber
    from MyTable
    where name = name_in
    FOR UPDATE;

    update MyTable
    SET value = value+2
    where name = name_in;
    Note that cnumber is returning previous value of vale
    No need of using cursors.
    
    Regards.
    Al
    
    Edited by: Alberto Faenza on Dec 5, 2012 10:46 AM
    Code modified                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 2. Re: Update multiple rows using WHERE CURRENT OF?
    971895 Journeyer
    Currently Being Moderated
    i am not to create the function..u should use this one in "pragma autonomous_transaction; " in declartion section...

    any way you are passing name,this name is unique column it will update only on column..
  • 3. Re: Update multiple rows using WHERE CURRENT OF?
    AlbertoFaenza Expert
    Currently Being Moderated
    968892 wrote:
    i am not to create the function..u should use this one in "pragma autonomous_transaction; " in declartion section...

    any way you are passing name,this name is unique column it will update only on column..
    What does it mean? For which reason he should use pragma autonomous_transaction;

    Regards.
    Al
  • 4. Re: Update multiple rows using WHERE CURRENT OF?
    976640 Newbie
    Currently Being Moderated
    Sorry Alberto Faenza.
    But if you pointed to caps lock in subject, i did it intentionally, not to shout but the way we generally write clauses in oracle.

    Well, you said it correctly to update multiple rows,
    But i think i need to explain more on my problem and your solution as well.

    Scenario is: if i am passing argument to function as
     name_in = 'Test1,Test2,Test3' 
    and don't want to remove
     where current of c1 
    Thanks.
  • 5. Re: Update multiple rows using WHERE CURRENT OF?
    jeneesh Guru
    Currently Being Moderated
    973637 wrote:
    Sorry Alberto Faenza.
    But if you pointed to caps lock in subject, i did it intentionally, not to shout but the way we generally write clauses in oracle.

    Well, you said it correctly to update multiple rows,
    But i think i need to explain more on my problem and your solution as well.

    Scenario is: if i am passing argument to function as
     name_in = 'Test1,Test2,Test3' 
    and don't want to remove
     where current of c1 
    Thanks.
    What is the reason for you to not to remove WHERE CURRENT OF...?


    A simple SQL update also will lock your rows..
    select * from mytable;
    
    UIDPK NAME            VALUE
    ----- --------------- -----
        1 Test1               1 
        2 Test2               2 
        3 Test3               3 
    
    create or replace procedure update_value(p_names varchar2) is
    begin
        update mytable set value=value+2
        where ','||p_names||',' like '%,'||name||',%';
    end;
    
    exec update_value('Test1,Test2');
    
    
    select * from mytable;
    
    UIDPK NAME            VALUE
    ----- --------------- -----
        1 Test1               3 
        2 Test2               4 
        3 Test3               3 
    Ideally you should not be using single string to pass the values, you should do it through an array..

    Edited by: jeneesh on Dec 5, 2012 3:37 PM
  • 6. Re: Update multiple rows using WHERE CURRENT OF?
    976640 Newbie
    Currently Being Moderated
    Thank you jeneesh for the response.
    To answer to your query is: The table (MyTable) from which i am selecting the row to be updated is common table.
    And in my case this is being frequently used by multiple threads.
    So when i select the "value" for a given "name", i wanted to make sure the value dis not get modified in between select and issuing update query.
    Hence used "where current of" along with "for update".
    Hope i am clear now.

    Thanks,
    Arfeen.
  • 7. Re: Update multiple rows using WHERE CURRENT OF?
    jeneesh Guru
    Currently Being Moderated
    But why should you select?

    Just update, that will keep the rows locked..

    And if it is already lockd, you wont be able to update..
  • 8. Re: Update multiple rows using WHERE CURRENT OF?
    AlbertoFaenza Expert
    Currently Being Moderated
    973637 wrote:
    Sorry Alberto Faenza.
    But if you pointed to caps lock in subject, i did it intentionally, not to shout but the way we generally write clauses in oracle.

    Well, you said it correctly to update multiple rows,
    But i think i need to explain more on my problem and your solution as well.

    Scenario is: if i am passing argument to function as
     name_in = 'Test1,Test2,Test3' 
    Hi Arfeen,

    Well, I'm answering according to the code you posted. And with the code you posted definitely your function will not work as I don't see any way you are scanning your values in a comma separated list.

    Also the code you posted has several errors. Please be sure to post valid code next time.

    Jeneesh already suggested a possible solution but I agree with him that the correct way passing the parameters to your function should be by using an array rather than a string with values comma separated.

    I confirm that for doing what you need you don't need the cursor. UPDATE will actually lock the rows until you commit so no other session can modify the same rows.

    However it seems you want to return with your function the previous values in cnumber. But how do you do with multiple rows (i.e. you update 3 rows) and you have only one value returned by your function?

    Please be clear on your requirements.

    Regards.
    Al
  • 9. Re: Update multiple rows using WHERE CURRENT OF?
    976640 Newbie
    Currently Being Moderated
    jeneesh, as per business requirement, need to hold the value before update for further manipulations.
    Also you see,
     SET vale = cnumber+2 
    In this this will be dynamic meaning it will not always incremented by 2. This is just for reference.
  • 10. Re: Update multiple rows using WHERE CURRENT OF?
    jeneesh Guru
    Currently Being Moderated
    973637 wrote:
    jeneesh, as per business requirement, need to hold the value before update for further manipulations.
    Business should only provide the requirements - Not technical requirements like this, but busines requirement - How to achieve it has to be decided by the techniacal team..

    What buisness requirement is pushing for you to "HOLD" the data? What is the advantage of that?
    Also you see,
     SET vale = cnumber+2 
    In this this will be dynamic meaning it will not always incremented by 2. This is just for reference.
    Please post your complete requirements, people here can respond ONLY based on the info you are giving..

    If it is not cnumber+2, what is the business logic to update the records?
  • 11. Re: Update multiple rows using WHERE CURRENT OF?
    kendenny Expert
    Currently Being Moderated
    First you need two types defined. If you don't already have a table of varchar2 and a table of number defined then you can use these
    CREATE OR REPLACE TYPE string_tab AS TABLE OF VARCHAR2(4000);
    CREATE OR REPLACE TYPE num_tab AS TABLE OF NUMBER;
    Now your function will be something like this.
    CREATE OR REPLACE FUNTION update_rows(name_list IN string_tab)
      RETURN num_tab IS
      CURSOR c1 IS
        SELECT value FROM mytable
         WHERE name IN (SELECT column_value FROM TABLE(name_list))
        FOR UPDATE;
      v_return  num_tab;
    BEGIN
      OPEN c1;
      FETCH c1 BULK COLLECT INTO v_return;
      UPDATE mytable
         SET value = value + 2
       WHERE name IN (SELECT column_value FROM TABLE(name_list));
      RETURN v_return;
    END;
    And don't commit in your function. Let the calling process decide whether to commit or not.
  • 12. Re: Update multiple rows using WHERE CURRENT OF?
    AlbertoFaenza Expert
    Currently Being Moderated
    973637 wrote:
    jeneesh, as per business requirement, need to hold the value before update for further manipulations.
    Also you see,
     SET vale = cnumber+2 
    In this this will be dynamic meaning it will not always incremented by 2. This is just for reference.
    It looks that your requirements are mutating every minute.

    As we might not know what you have in mind I would suggest to explain exactly your requirements as you seem not to have clear ideas of what they are.

    You posted a function receiving a single parameter (VARCHAR2) and returning a single parameter (NUMBER).
    Then you said you want to pass a list of input values separated by comma ans I commented that in this way you need to know how to get your output as you have a single value.
    Now you are asking to have a dynamic value to add.

    We can help you on your code but it seems you have a vague idea of what your code should do.
    Post all of your requirements and avoid adding one more each post as we did not understand what you are asking.

    So be clear in this way:

    a) Do you want a function to update multiple rows? Do you want to have multiple value of name column in input of your function?
    b) If you update multiple rows, do you want to return from the function only one value related to the last row you have updated or do you want to return an array with last value for each name you have updated?
    c) what if the input list contains duplicated value (i.e.; 'test1, test1')?
    d) You said you want to update value column by a dynamic value. Is this value going to be the same for all rows you want to update or do you have the same value for all the rows you update with one function call?

    Regards.
    Al
  • 13. Re: Update multiple rows using WHERE CURRENT OF?
    976640 Newbie
    Currently Being Moderated
    Hello All,

    Thank you all for responding on this issue. Based on the conversation, i am modifying my code and will explain exact need.

    Some points to be noted-

    1. I don' anything to be returned by function. I am good to with void.
    2. The "value" will be set based on the "name" we are passing.
    3. Since this table is common to all thread or program, so make what a particular thread selects, it updates on same value.

    And here is the reason behind need for this-
    We are using JPA as ORM. This uses a table (a common table) where it puts all the entities to be persisted with their last value used.
    In my example MyTable is that table used by JPA.
    as i describe it
     1, Test1, 10
    2, Test2, 20
    3, Test3, 30
    4, Test4, 40
    Test1, Test2... are name of entities. So if i say Test1 has "value" 10, means if a thread comes to insert a row in Test1, the uidpk of Test1 will be used
    by that thread must 11. That thread is responsible to use next available uidpk from Mytable and update the same. So Thread used value as 11 for Test1,
    It will update the same on MyTable as well. If we execute
     select value from mytable where name='Test1'
    will return 11. And so on. Depending on application thread can insert more than one row at a time.

    If you are still with me, a big thank you for this.

    So point is, if application uses JPA and why i should write such code. To answer this some application are using Non-JPA technology. Application which are using Non-JPA technology can also insert row in Test1, Test2... and there i need to select last value used for uidpk and update the value used and make sure no other thread get dirty value.

    Hope i am clear now.

    Thanks,
    Arfeen.
  • 14. Re: Update multiple rows using WHERE CURRENT OF?
    6363 Guru
    Currently Being Moderated
    973637 wrote:

    We are using JPA as ORM. This uses a table (a common table) where it puts all the entities to be persisted with their last value used.
    I suppose you deserve some sympathy for having to deal with a completely non-relational data model. Have you considered saving database license fees and using flat files for persistence instead? It is not like you will be able to get any benefits from database functionality either way.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points