Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    May 2010
    Posts
    56

    Unanswered: How to know which rows got updated after Update Command

    Hi,

    I have to update a table, say T1 based on certain crieteria.
    Just after updation I need to know which all rows were updated.
    And return these rows through a refcursor.

    So how can I find which rows were updated in the table????

    A real life scenario can be:

    Create Procedure p1( p_recordset out sys_refcursor)
    IS

    BEGIN


    UPDATE T1
    SET C1= 'P', C2= 'O'
    WHERE
    (C3= 'A' AND C4='u'............some conditions);

    COMMIT;

    OPEN p_recordset for SELECT * from T1;(HERE I need to return which values were updated in the previous Update command)..

    END;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >OPEN p_recordset for SELECT * from T1;(HERE I need to return which values were updated in the previous Update command)..

    WHERE (C3= 'A' AND C4='u'............some conditions);
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2010
    Posts
    56

    Thanks and Sorry for not putting it right

    Hi anacedent,

    Thank you very much for answering.
    And sorry I didn't put my question right.

    In the Update command when I am updating column C1 AND C2.
    I have the same columns in where clause as well.
    So how do I get them after the updation?

    Thanks,
    Sudhir

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Ana answered your question.

    select * from TABLE where (C3= 'A' AND C4='u'............some conditions);
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    May 2006
    Posts
    132
    You can consider the RETURNING INTO Clause.

  6. #6
    Join Date
    May 2010
    Posts
    56

    Sorry but still not working..Think..

    Hi Everyone,

    It's not that simple..Please think before answering..:-)

    If you look at the code below.
    If the columns, C1 and C2 have been updated from 'A' and 'u' to 'P' and 'O' respectively.
    In the refcursor, I can't do select * from T1 where c1='P' and c2='O',,as there may be some other rows too which were already with those values.
    Or some other user does it while we are updating this.

    I NEED TO find those rows only which have been updated by this update statement..


    Create Procedure p1( p_recordset out sys_refcursor)
    IS

    BEGIN


    UPDATE T1
    SET C1= 'P', C2= 'O'
    WHERE
    (C1= 'A' AND C2='u'............some conditions);

    COMMIT;

    OPEN p_recordset for SELECT * from T1;(HERE I need to return which values were updated in the previous Update command)..

    END;

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by sudhirc212 View Post
    I NEED TO find those rows only which have been updated by this update statement..[
    That's precisely what the RETURNING INTO clause suggested by ebrian will do.

    If you don't trust that advice, simply run the SELECT statement suggested by anacedent and The_Duck before updating the table and store the rows in a collection.

  8. #8
    Join Date
    May 2010
    Posts
    56

    Thanks..But..

    Actually. I have thought of RETURNING clause into a collection.

    But it doesn't look good. As I can't use a collection to Select From in a REFCURSOR..

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by sudhirc212 View Post
    Actually. I have thought of RETURNING clause into a collection.

    But it doesn't look good. As I can't use a collection to Select From in a REFCURSOR..
    But you can return a collection

    The only "drawback" is, that the caller will need to adjust the SQL, as it then becomes a simple SELECT * FROM table(your_procedure)) instead of processing a REFCURSOR

  10. #10
    Join Date
    May 2010
    Posts
    56

    Thanks..But..

    Yes.
    I have thought of returning a collection as well.
    But it's not the right way to do things as it requires more bandwidth to actually pass the data. And it has incompatibilty issues too rather than REFCURSOR.

    So I think you have actually understood the issue here.
    If in these conditions I have to return the data through a refcursor only. What do you think is the best way to implement it.

    Thanks..

  11. #11
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by sudhirc212 View Post
    But it's not the right way to do things as it requires more bandwidth to actually pass the data.
    Why do you think so?
    The data that will be sent over the network should be the same.
    And it has incompatibilty issues too rather than REFCURSOR.
    Which one - apart from the change in the SQL that I mentioned?
    My personal opinion is, that processing a "simple" select is a lot easier than using a refcursor, but then that might just be me...

    If in these conditions I have to return the data through a refcursor only. What do you think is the best way to implement it
    Have you tried to open the refcursor using the SELECT before updating the table?
    I'm not sure that it will work (actually I doubt that it will) but it's worth a try if you insist on not using a collection.

    The only other way I can think of, is to put the rows into a temp table and populate that temp table before running the update, then returning the refcursor from that temp table.

  12. #12
    Join Date
    May 2010
    Posts
    56

    Thanks Shammat

    Thanks Shammat..

    I appreciate your help.
    I was thinking of going by using temporary tables only. Thanks for suggesting and validating that..
    And reason of not passing a collection is that if suppose lakhs or millions of rows are to be updated. It's better to pass them as a pointer to the cursor i.e. refcursor rather than passing this huge amount of data i.e. collections.

    Regards....

  13. #13
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Just curious, why is it so important to return a list of modified rows?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  14. #14
    Join Date
    May 2010
    Posts
    56

    The requirement is ..

    Hi All,

    I have a question on update.

    Suppose we open two sessions of TOAD on our machine.
    And try to update t1 from both sessions nearly simultaneously using the same proc.
    If T1 has 100 rows to be updated according to the proc, then is it a possibility that both sessions may update say 50-50 rows.
    Or the session which hits first, will update the entire 100 and then only will the other session get access to the table?

    And @Beilstwh..Thanks.. I am trying to find why exactly is the requirement to return the same rows which have been updated using the REFCURSOR..
    Regards..

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >is it a possibility that both sessions may update say 50-50 rows.
    Never do 50 - 50.
    SQL is NOT self-aware; just dumb code.
    Each session updates all 100 rows.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •