Persist the value of one column in two simultaneous update

Question

I have a column in Table Customer_Details, say Phone_Number. There would be multiple process/queries that would update the column 'Phone_Number' simultaneously. I would like to retain the value of 'Phone_Number' column after each simultaneous update. The default scenario would be: the latest update on 'Phone_Number' column is retained.

---

1. Solution 1 (New columns to be added): Have different columns for differnt process to be updated.

Eg. New columns would be inserted in table 'Customer_Details' called as 'Phone_Number_Web' and 'Phone_Number_Mobile' and then the two queries would update respective columns. And while fetching values, these two columns data would be merged.

2. Solution 2 (Lock the table: Not an option): Lock the table till one query updates and commits and then before second update take value from column 'Phone_Number' and merge it with second update and then commit.

As table lock is not an option so apart from 'Inersting new columns (Solution 1)' is there any other solution???

---

P.S: Going further the number of queries/updates might increase on the table.

Thanks

Answer 1

Per your comments, what you state is that you want to append the new values to the existing values whenever a row is updated.

so, what you could do is, use the concatenation operator in the SET clause of the UPDATE statement such that you always append the new values to the existing values.

For example,

SQL> create table t(a  varchar2(100));

Table created.

SQL> insert into t(a) values('1234');

1 row created.

SQL> select * from t;

A
--------------------------------------
1234

SQL> update t set a = a||';'||2345;

1 row updated.

SQL> update t set a = a||';'||3456;

1 row updated.

SQL> select * from t;

A
--------------------------------------
1234;2345;3456

SQL>