Oracle Sql Statement for unique timestamp for each row

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

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

  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.

Leave a Comment