Search code examples
sqloracle

Oracle Sql Statement for unique timestamp for each row

We have following table on an Oracle DBMS (used by a legacy application), where a timestamp is part of the key (I know there would be better ways...)

TABLE ITEM_HISTORY (
 ITEM_ID number,
 MY_TIMESTAMP TIMESTAMP(7),
 ... 
 PRIMARY KEY (ITEM_ID, MY_TIMESTAMP));

Only the combination of ITEM_ID and MY_TIMESTAMP is unique. On a running system we need to guarantee now, that every timestamp is unique, because we have to make some corrections of corrupt data and with given data we violate the unique key constraint. With a simple MY_TIMESTAMP = SYSTEMTIMESTAMP update, all rows will have the same timestamp.

How can I update my Table with SQL, so that every row has an unique timestamp?

Update / Explanation:

Because data got corrupted at our client, I only can try to correct the items as good as possible. In some cases, items (with different ITEM_IDs) in the corrupted table will be corrected to the same item with same ITEM_ID. To make this update, I must guarantee before that the timestamps are different.

Solution

  • The following UPDATE statement will guarantee that each row has a unique MY_TIMESTAMP value, by increasing the milliseconds by the rownum value.

    EDIT: After Alessandro Rossi pointed out that there could be duplicate values, the following query has been modified to use SYSTIMESTAMP for the update.

      UPDATE ITEM_HISTORY 
  SET my_timestamp = SYSTIMESTAMP + NUMTODSINTERVAL(rownum/1000, 'SECOND');

    However, it is recommended that you use an alternative strategy, such as adding another column to store the key value.