Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: Can I trust this statement? (update test set mystring = replace(mystring,'.','*');)?

    I need to replace some values in a column. If I run the following statement:

    update test set mystring = replace(mystring,'.','*');

    Will it holdup my data's integrity even if updates and deletes are occuring at the same time? Will it execute record by record.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Can I trust this statement? (update test set mystring = replace(mystring,'.','*');)?

    It will (attempt to) lock every record that existed at the time your transaction began. If others are updating/deleting at the same time then:
    - if they lock the record first, your query is blocked until they commit/rollback
    - if you lock it first, their query is blocked until you commit/rollback
    Once your update completes and you commit, every row it updated will be saved. There could be new rows that have been inserted by others after your transaction began, these won't have been updated.

    What are you afraid might happen?

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Can I trust this statement? (update test set mystring = replace(mystring,'.','*');)?

    Regarding integrity enforced by constraints (primary key, unique, check, foreign key) - these are guaranteed not to be violated.

  4. #4
    Join Date
    Feb 2004
    Posts
    2
    I guess I was concerned that the context of the update would be for the column alone and that my data might get 'shuffled' up/down based on inserts or deletes. Your reply makes sense and is very appreciated. I'm obviously new to Oracle.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, you need have no worries on that score. Oracle is a sophisticated DBMS, you'll grow to love it I'm sure

Posting Permissions

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