1 2 Previous Next 16 Replies Latest reply: Dec 6, 2012 12:23 AM by 976640 RSS

    Update multiple rows using WHERE CURRENT OF?

    976640
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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